cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
revstevens Frequent Visitor
Frequent Visitor

Fixed count that ignores a single slicer

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 NameUser PositionCount of IDFixed Count
Adama59
Boba49
Claireb22

 

It only works up until I slice by User Name. At that point I get something similar to this:

User NameUser PositionCount of IDFixed Count
Adama55

 

Here's my data:

Table1-

IDDateUser NameUser Position
1 October 1, 2018 Adam a
2 October 2, 2018 Adam
3 October 3, 2018Bob 
4 October 4, 2018 Adam
5 October 5, 2018Bob 
6 October 6, 2018 Adam
7 October 7, 2018Bob
8 October 8, 2018Claire 
9 October 9, 2018 Bob
10 October 10, 2018 Adam
11 October 11, 2018 Claire

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
v-jiascu-msft Super Contributor
Super Contributor

Re: Fixed count that ignores a single slicer

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

Fixed_count_that_ignores_a_single_slicer

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Super User
Super User

Re: Fixed count that ignores a single slicer

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

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Highlighted
revstevens Frequent Visitor
Frequent Visitor

Re: Fixed count that ignores a single slicer

I'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 NameUser PositionCount of IDFixed Count
Adama55
Boba44

 

Fixed Count should still be showing 9 in both rows.

 

Any other ideas? Cause I am alllllll out.

Super User
Super User

Re: Fixed count that ignores a single slicer

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

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




revstevens Frequent Visitor
Frequent Visitor

Re: Fixed count that ignores a single slicer

Hoookay...

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.

v-jiascu-msft Super Contributor
Super Contributor

Re: Fixed count that ignores a single slicer

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

Fixed_count_that_ignores_a_single_slicer

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
revstevens Frequent Visitor
Frequent Visitor

Re: Fixed count that ignores a single slicer

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!

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 30 members 916 guests
Please welcome our newest community members: