cancel
Showing results for
Did you mean:
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 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.

1 ACCEPTED SOLUTION

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

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

## 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

Proud to be a Datanaut!

Highlighted
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 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.

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

Proud to be a Datanaut!

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.

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

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.
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!

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

#### PBI Community Highlights

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

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 30 members 916 guests
Recent signins: