Counting Columns with specific criteria in a related table

[Anonymous]

Hello everyone,

I'm very new to Power Pivot and DAX. I've started looking into the subject as I've got an assignment with way to much data for a usual excel.

I can't figure out the proper syntax for two columns I want.

So, I've 3 tables with table 3 being a sort of reporting dashboard. I've figured out the 2nd column of my dashboard using =COUNTROWS(RELATEDTABLE('Table 1')) but I'm lost for the other 2 columns (N. of IDs with products) and (N. of Product 1 at Level 2).

Table 1

ID	Segment
id01	A
id02	B
id03	A
id04	C
id05	B
id06	B
id07	A

Table 2

ID	Product 1	Product 2	Product 3
id01	Level 1	Level 3
id02	Level 4	Level 1	Level 2
id03	Level 2
id04
id05
id06	Level 2	Level 2	Level 1
id07	Level 3

Table 3

Segment	N. of IDs	N. of IDs with products	N. of Product 1 at Level 2
A	3	3	1
B	3	2	1
C	1	0	0

I hope you can help me out.

M.

[JW_van_Holst]

First of all you have to unpivot Table2 in powerquery and set a relationship between Table1 (one side) and Table2 (many side). The measures below will give you the following results.

Measure1 = COUNTROWS(Table1)

Measure2 = 
VAR __filter = FILTER(Table2, Table2[Level] <> BLANK())
RETURN
CALCULATE(DISTINCTCOUNT(Table2[ID]),__filter)

Measure3 = 
VAR __filter = FILTER(Table2, Table2[Product] = "Product 1" && Table2[Level] = "Level 2")
RETURN
CALCULATE(DISTINCTCOUNT(Table2[ID]),__filter)

[Anonymous · Accepted solution]

Hey I finally got it. Just forgot to add a filter in the end. So for measure 2 it's

Measure2 = 
VAR __filter = FILTER(Table2, Table2[Level] <> BLANK())
RETURN
CALCULATE(DISTINCTCOUNT(Table2[ID]),RELATEDTABLE(Table1),__filter)

[Anonymous]

Thanks for the quick reply.

I followed everything as instructed but I'm getting only the total for measures 1 and 2. How do I relate those to my segments ?

Is it the one to many relationship ? If i understand how it works, the relationship's only for the ID column.

Again thanks so much.