DistinctCount ONLY the lowest values of a column in case of multiple rows

[FVZ]

I'm looking for a DAX calculation which does DISTINCTCOUNT with a certain condition. If a Customer has two different Priorities, it should only do a count on the lowest Priority. To demonstrate with dummy data:

Customer Priority

Jan	5
Jan	1
Tom	2
Dirk	6

You see that Customer Jan has two rows, one with Priority 5 and 1. In this case I want to DISTINCTCOUNT the Customer only for the lowest Priority, being 1 in this case. The end result should look as follows. 

Thanks in advance!

[Anonymous · Accepted solution]

Hi @FVZ 

Thank you very much FreemanZ and dharmendars007 for your prompt reply.

Try this:

Create measures.

Lowest Priority = 
CALCULATE(
    MIN('Table'[Priority]),
    ALLEXCEPT('Table', 'Table'[Customer])
)

Distinct Customer Count = 
CALCULATE(
    DISTINCTCOUNT('Table'[Customer]),
    FILTER(
        'Table',
        'Table'[Priority] = 'Table'[Lowest Priority]
    )
)

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FreemanZ]

hi @FVZ ,

try like:

measure =

VAR _table =

ADDCOLUMNS(

    VALUES(data[Customer]),

     "LowestPriority",

      CALCULATE(MIN(data[Priority])

)

VAR _result =

CALCULATE(

   DISTINCTCOUNT(data[Customer]),

   TREATAS(

      _table,

      data[Customer],

       data[Priority]

    )

)

RETURN _result

[FVZ]

It works to a certain extend, but only as a total. If I put the Priority column on the axis, I still see it counting double. Any advice/adjustments on the code? Would appreciate it!

[dharmendars007]

Hello @FVZ , 

Please try the below measure to count customers for lowest priority.

Lowest Priority Customer Count =
COUNTROWS(
SUMMARIZE('YourTable','YourTable'[Customer],"LowestPriority",
                      MIN('YourTable'[Priority]))

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[FVZ]

It works to a certain extend, but only as a total. If I put the Priority column on the axis, I still see it counting double. Any advice/adjustments on the code? Would appreciate it!