Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello. I have a Card visual showing the number of rows in a fact table and would like the number to change depending on certain dates selected from a date slicer using the measure:
Counts = CALCULATE(countrows('Case Data'), ALLEXCEPT(Dates, Dates[Date]))
And this works fine. The problem I have is that I also have another visual (a Table) in the report which breaks down the respective 'Counts' by different Areas and counts their respective figures making up the overall total of the 'Counts' measure. However, when one of the Areas are clicked, this filters through and reduces the overall 'Counts' because it is then only showing the overall count for that particular Area (so it is filtering the count by the Area, and by dates selected). But I do not want the selected Area to filter the overall Count Measure outside of its own visual table, as i want to make an easy visual comparison between the sum and the parts.
I thought using ALLEXCEPT might have solved this but it has not done so. Any help would be greatly appreciated. Thank you.
Please see below, where the overall count has changed after the North Area is selected in the visual below:
Solved! Go to Solution.
Is the Area Name field from the Case Data table or is it from another table (dimension table for example)?
A much simpler option is just to turn off the interaction between the table visual and the card visual
Proud to be a Super User!
Paul on Linkedin.
I can't test this but:
1) not sure why you need ALLEXCEPT for the card visual. If your Date Table has a relationship with your Case Data, you should be able to use
Counts = COUNTROWS(Case data)
the date slicer will filter the rows in the case data down to the slicer selection.
2) then, to ignore the Area filter, use:
Counts all areas = CALCULATE([Counts], ALL(Case data [Area]))
If you want it all in one measure:
Counts = CALCULATE(COUNTROWS(Case data), ALL(Case data [Area]))
Proud to be a Super User!
Paul on Linkedin.
Thank you for your reply Paul, it is much appreciated.
I was using the ALLEXCEPT function to try to prevent any clicks on the Areas visual from affecting the (overall) Counts total.
And just to confirm, the counts are working fine for any date selections.
Anyway, following your reply I have now changed the Counts measure to be
Is the Area Name field from the Case Data table or is it from another table (dimension table for example)?
A much simpler option is just to turn off the interaction between the table visual and the card visual
Proud to be a Super User!
Paul on Linkedin.
Thank you Paul, I'm fairly new to Power BI and wasn't too sure how the Interaction settings worked, but now you have mentioned it and I have tried it I have it working. Thank you very much.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |