[DAX Help] Counting rows from related table with conditions

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:

 

 

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

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.

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