Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
revstevens
Regular 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

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.

View solution in original post

6 REPLIES 6
MFelix
Super User
Super User

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


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

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


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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.

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.