Forum Discussion
Dynamic Count of Rows
Hi PBI Community,
I'm wondering whether you could help me with a problem I came across recently. I do have a lengthy DAX measure to correctly calculate a metric at different hierarchy levels and in a different filter context, however it's not always working right. Having investigated the issue I discovered the problem is with a small inner DAX measure that is supposed to calculate count of rows. The scenario is similar to the below:
DAX behind CountRows is:
CountRows = CALCULATE(COUNTROWS(Table1), ALLEXCEPT(Table1, Table1[Product]))
This gives count per product which is great, however I would like this count to adjust based on filters I'm applying on Table1 i.e. If I choose Type='Beginner' I would like to see the below output:
Product Category Type Price CountRows Snow Board On Piste Beginner 100 2 Snow Board On Piste Beginner 120 2 Wake Board Smooth Beginner 150 1
Current DAX measure will always show 3 rows for Wakeboard and 4 rows for SnowBoard like this:
I would really appreciate your help on this.
Many Thanks,
Anonymous try this measure, change table and column name as per your data model.
Count of Products = VAR __currentProduct = SELECTEDVALUE( Table3[Product] ) RETURN CALCULATE( COUNTROWS( Table3 ), ALLSELECTED( Table3 ), Table3[Product] = __currentProduct )
3 Replies
Anonymous try this measure, change table and column name as per your data model.
Count of Products = VAR __currentProduct = SELECTEDVALUE( Table3[Product] ) RETURN CALCULATE( COUNTROWS( Table3 ), ALLSELECTED( Table3 ), Table3[Product] = __currentProduct )Thanks a lot parry2k,
It did work... there were a couple changes I had to do to make it work for me as I'm connecting to SSAS compatibility level 1103 (no variables and selectedvalue function). The exepression that has worked for me is:
Count of Products = CALCULATE( COUNTROWS( Table1 ), ALLSELECTED( Table1 ), Table1[Product] = IF (
HASONEVALUE ( Table1[Product] ),
VALUES ( Table1[Product] )
) )Thanks again,
Lupa
attempting to use this solution, but the measure returns empty calculations. Is this measure possible with a table as a new measure column? If I hard code the value selection, the countrow works, but using selected value is just blank.
