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.
I'm trying to get a fixed count of tasks completed by people by position. I've got three slicers: User Position, User Name, Date.
When the output is a card, it works great no matter how much I slice it. However, when I try to create a table like this:
User Name | User Position | Count of ID | Fixed Count |
Adam | a | 5 | 9 |
Bob | a | 4 | 9 |
Claire | b | 2 | 2 |
It only works up until I slice by User Name. At that point I get something similar to this:
User Name | User Position | Count of ID | Fixed Count |
Adam | a | 5 | 5 |
Here's my data:
Table1-
ID | Date | User Name | User Position |
1 | October 1, 2018 | Adam | a |
2 | October 2, 2018 | Adam | a |
3 | October 3, 2018 | Bob | a |
4 | October 4, 2018 | Adam | a |
5 | October 5, 2018 | Bob | a |
6 | October 6, 2018 | Adam | a |
7 | October 7, 2018 | Bob | a |
8 | October 8, 2018 | Claire | b |
9 | October 9, 2018 | Bob | a |
10 | October 10, 2018 | Adam | a |
11 | October 11, 2018 | Claire | b |
Here's my measure:
Fixed Count =
Fixed Count = CALCULATE( DISTINCTCOUNT( Table1[ID] ), ALLSELECTED( Table1[User Name] ) )
I've tried every variation of that measure that I can come up with and I'm stumped. Any help would be appreciated.
Solved! Go to Solution.
Hi @revstevens,
I would suggest you create an independent date table. Please refer to the demo in the attachment.
Fixed Count =
CALCULATE ( DISTINCTCOUNT ( Table1[ID] ), ALL ( Table1[User name] ) )
Best Regards,
Dale
Hi @revstevens,
To what I can understand you want to show on the total count the number the count of a certain user no matter what are the filters so for Adam it will always show 9 correct?
In this case you need to overcome the context of the measure, when you use ALLSELECT you are only getting all the selected values of the information so it works for all slicers that are not related with the column you refer in this case user name.
You need to use the ALL function redo your measure to:
Fixed Count = CALCULATE( DISTINCTCOUNT( Table1[ID] ), ALL( Table1[User Name] ) )
Should work as expected, Only one question when you have the date slicer active do you still want to have 9 or should be adjust to those dates? If the answer is keep 9 the measure should be changed to this:
Fixed Count = CALCULATE( DISTINCTCOUNT( Table1[ID] ); ALL( Table1[User Name];Table1[Date]) )
Regards
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI'd like to retain the date filter. So, I tried this (as suggested):
Fixed Count = CALCULATE( DISTINCTCOUNT( Table1[ID] ), ALL( Table1[User Name] ) )
Filtering the date to 10/1/2018-10/10/2018, and User Position to a gives me this:
User Name | User Position | Count of ID | Fixed Count |
Adam | a | 5 | 5 |
Bob | a | 4 | 4 |
Fixed Count should still be showing 9 in both rows.
Any other ideas? Cause I am alllllll out.
Hi @revstevens,
That is the second measure I have in the ALL formula you need to add also the date column.
Check the last measure on my previous answer
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHoookay...
I think we're getting somewhere. I used this, as suggested (I think I misread the previous comment - apologies for that):
Fixed Count = CALCULATE( DISTINCTCOUNT( Table1[ID] ), ALL( Table1[User Name],Table1[Date] ) )
It looks like, what that is doing is disregarding the date filter though. When I adjust the date to 10/3/2018-10/11/2018 the Fixed Count should be 7, but I'm still getting 9.
BTW, thank you, so much, for helping me out with this. Even if we never get it completely sorted out.
Hi @revstevens,
I would suggest you create an independent date table. Please refer to the demo in the attachment.
Fixed Count =
CALCULATE ( DISTINCTCOUNT ( Table1[ID] ), ALL ( Table1[User name] ) )
Best Regards,
Dale
If it were possible to kiss someone over the internet, and my wife wouldn't yell at me for doing it... I'd kiss you. That worked! Thank you, so 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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |