new to dax formula

[Lolo]

Hello,

I have a basic need to count value between 2 linked tables

For example,

Table 1
val category idTable2
1 A 1
2 A 1
3 B 2


Table 2
id
1
2

I want ot add
* a measure in the table 2, to count the nb of val of table 1 where category = A
* a measure in the table 2, to count the nb of val of table 1 where category = B

Result expected (in a pivot table)
id(table 2) nb A nb B
1 2 0
2 0 1

Is it possible to do that ?

Thank you

 

[Chihiro]

Something like below?
TypeA:=CALCULATE(COUNTA([Category]),FILTER(table2,[ID]=1))
TypeB:=CALCULATE(COUNTA([Category]),FILTER(table2,[ID]=2))





Though you don't need measure if it's simply for this purpose.

 

[Lolo]

Thank you,

Indeed in this case no need of measure, just need to count category by id.

But what I wanted to do is to understand was how to create a measure in a table2, based on a filter on a column of the table1. My example is indeed not really good.

Nevermind, in my example, I expected to create the 2 measures in the table 2, not in the table 1 ? Like this
TypeA:=CALCULATE(COUNTA(Table1[Category]),FILTER(Table2,[id]=1))
TypeB:=CALCULATE(COUNTA(Table1[Category]),FILTER(Table2,[id]=2))

Is it correct to do that?

 

[Lolo]

Actually, it will be better if I send you a file with the right example.
All is explained inside.

Thank you for you help

 

[Chihiro]

I'm a bit confused.

What are you trying to do? Identify row where Serie & Type doesn't match in Table1? I.E. Unit 3, Type BB, SERIE_ID 1000?

 

[Lolo]

Yes. But I just want a total of units (with type#) by SERIE_ID. not the detail

I know how to do by adding a calculated column in table1 (Where I would compare Type of each unit with type of RELATED serie), but I'm wondering if it can be done directly with a measure without adding any calculated column in Table1.

Hope I'm clear

 

[Chihiro]

This sort of thing is best handled at PowerQuery stage before data is loaded to model.

And no this can't be done in Measure. As this sort of calculation requires row context (in your pivot example, there is no context in which measure can be evaluated since "BB" is non existent in primary key field).

As general rule while Measure should be used in majority of situation, Calculated column instead of Measure should be used in following instances.

1. You need results in a slicer, or need it in Rows/Columns field (not in values field).
2. Expression must be evaluated strictly in current row context (ex: Price * Quantity).
3. Need bucketing/categorization of data. (Edit: Though I'd do this in PowerQuery rather than PowerPivot in most cases).

 

[Lolo]

I fully understand, thank you. At power query stage, this will grow the size of the data, but ok, I will.

I admit I hacve now a certain knowledge of power query and M language, but DAX formulas is still a little bit complex for me.

 

[r2c2]

@Lolo
Power Pivot can be a tricky nut to crack if you are not used to measure / tabular thinking.

To clarify, measures can be in any table, but their values will depend on the context where they are placed. So, you need not say "I want a measure in table 2". It will give the same result whether you attach the measure in table 1 or table 2.

Now, while you can get the result thru PQ option as suggested by @Chihiro, you can also use the excellent RELATEDTABLE feature to findout how many rows in Table1 have a different type than specified in Table2 for selected Serie.

Load both tables to PQ and link them on Serie column.

See below measures.

Related Row count: = COUNTROWS(RELATEDTABLE(Tableau1))
This gives 3 for 1000 and 1 for 2000
Unmatched type count: = COUNTROWS( FILTER(RELATEDTABLE(Tableau1), Tableau1[TYPE] <> FIRSTNONBLANK(Tableau2[TYPE],true)))

This gives 1 for 1000 and blank for 2000.

Remember, FILTER() returns a table (that is filtered with the criteria you specify. We then just count rows on that table to see how many items have different type.

Hope that helps.