[DAX Help] Counting rows from related table with conditions

[cretak]

Hi all,

I am still finding my way in the world of PowerBI 🙂 Here my issue:

I have two tables who are related with a 1:M

Table Orders:

Order ID	Name
1	Neighbour
2	Child
3	Mom

Table LineItems:

LineItem ID	Order ID	Item Type
1	1	Pants
2	1	Underwear
3	2	Pants
4	3	Underwear
5	3	Underwear

My result should simply look like this:

Table Orders:

Order ID	Name	Count Underwear
1	Neighbour	1
2	Child	0
3	Mom	2

It means I just want to add the count of Underwear per order. All I got is the total count so far. I didnt get the relationship working in DAX.

Sorry for the inconvenience and many thanks for the help and support,

Oli

[cretak]

I am currently travelling and cant test it! Will come back to you over the course of the week! Many thanks for the feedback already

[Anonymous]

Hi,

Thanks for the solutions @muhammad_786_1  and @audreygerred offered, and i want to offer some more infotmation for user to refer to.

hello @cretak . you can create a measure.

MEASURE =
VAR a =
    COUNTROWS ( FILTER ( LineItems, [Item Type] = "Underwear" ) )
RETURN
    a + 0

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[muhammad_786_1]

Hi,

You can also create a calculated column directly in the Orders table to achieve this by using this DAX:

Best Regards,
Muhammad Yousaf

If this post helps, then please consider "Accept it as the solution" to help the other members find it more quickly.

LinkedIn

[audreygerred]

Hi! First, I made a measure for the total row count on the LineItems table:

Total Row Count = COUNTROWS(LineItems)

Next, I created the measure to look for only Underwear. I used the quick measure feature and chose filtered value. First, click the three dots next to the Total Row Count measure you just made. Then, select New quick measure. From the drop-down, select Filtered value, ensure that Total Row Count measure is in the Base Value well, then in the filter area put in Item Type and select Underwear from the drop-down - click Add.

 You now have your new measure with this for the DAX: 

Total Row Count for Underwear =

CALCULATE([Total Row Count], 'LineItems'[Item Type] IN { "Underwear" })

Now, you can make your visual: