Solved: Re: Dynamic Count of Rows

Anonymous · ‎04-05-2019

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,

parry2k · ‎04-05-2019

@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 
)

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

parry2k · ‎04-05-2019

@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 
)

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

mc2863 · ‎06-20-2021

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.

Anonymous · ‎04-05-2019

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