Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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,
Solved! Go to Solution.
@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.
@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.
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.
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
90 | |
81 | |
65 | |
65 | |
61 |
User | Count |
---|---|
170 | |
115 | |
100 | |
73 | |
67 |