Count of distinct Values inside the column

[Anonymous]

Hello,

I have a scenario where i need the distinct value count in a column according to the dates selected in the slicer.

Example:

I have used the following DAX measure( count_2) to get the count of distinct item locations according to the dates selected in the slicer.

But as you can see in the screenshot it shows the count of item location "ZZ"  as 1.

I want it show 2 as it is appearing two times in the output.

Here is the date slicer applied:

Any help would be much appreciated.

Thanks

[Anonymous · Accepted solution]

You get 1 because for ZZ the rows are different. You distinguish them with "last user update." One has SANJAY and the other one IGRAHAM. This is why you see 1.

[mahoneypat]

The columns you add to your table act as filters.  In your case, that includes the Date and Branch columns.  To remove specific filters, you need to use a variant of the ALL() function (or REMOVEFILTERS()).  To get 2 from your data, I think you will need the following

New Count = CALCULATE([count_2], ALL(Table[Branch], Table[Branch Description]), ALLSELECTED('Date'[Date])

Note that the Date one uses ALLSELECTED.  That will remove the filter on that column but just in the table (it will keep the filter on the Dates selected in your slicer).

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Hello @mahoneypat 

This is giving me exactly the same as count_2

[Anonymous]

Hello @mahoneypat 

I can remove count_2 as this might not be required, i basically need a count of different item locations in the report as per the selected dates.

I need to get same the output similar to the filter of item Location in filter pane:

[harshnathani]

Hi @Anonymous ,

Try if this works

LocationsCount =
COUNTROWS (
    FILTER (
        ALL ( ItemLocation[itemlocation] ),
        ItemLocation[itemlocation]
            = MAX ( ItemLocation[itemlocation] )
    )
)

Regards,

Harsh Nathani

[Anonymous]

Hi @harshnathani 

This does not work:

The measure count_2 works as expected when i remove all the fields:

But as soon as i bring branch or any other field it fails to work.

I need to ingore all the other fields as filters .