topic Re: Slicer for a DISTINCTCOUNT metric in DAX Commands and Tips

Slicer for a DISTINCTCOUNT metric

pedroccamaraDBI — Wed, 30 Mar 2022 18:06:21 GMT

Hello all
I hope everyone's ok, safe and healthy.
I have this simple measure that calculates the distinctcount of a contract, by a certain date

Contracts (by initial risk date) =

CALCULATE(

DISTINCTCOUNT ( 'Contracts'[Contractid]),

USERELATIONSHIP( Dates[Date],'Contracts'[INRiskDate]) )

My visual has all clients and the number of contracts for each
It works fine but now i would like to add filter with the number of contracts so i can choose the clients that has only 1 or 2 or 10 contracts

Thanks a lot in advance

Re: Slicer for a DISTINCTCOUNT metric

amitchandak — Thu, 31 Mar 2022 02:16:48 GMT

pedroccamaraDBI , Try a meausre like

Client GT contract NO =
var _cnt = CALCULATE(
DISTINCTCOUNT ( 'Contracts'[Contractid]),
USERELATIONSHIP( Dates[Date],'Contracts'[INRiskDate]) )
var _no =2
return
countx(filter(summarize('Contracts',Clinet[client], "_1",_cnt),[_1] >= _no),[Clinet])

Re: Slicer for a DISTINCTCOUNT metric

pedroccamaraDBI — Thu, 31 Mar 2022 09:36:58 GMT

Hello amitchandak
The first part it's clear that's my measure above.
Could you please explain the 2nd part, the countx?
For some reason it's not working and it shows me a value but i can't put it on a filter

Re: Slicer for a DISTINCTCOUNT metric

v-chenwuz-msft — Wed, 06 Apr 2022 05:10:59 GMT

Hi pedroccamaraDBI ,

Maybe you can try just put it into filter pane and set the measure show itmes which is 1 or 2 or 10.

But if you want use slicer to control this number, a table contains all the number from 1 to max(distinctcount( contractID])) is necessary. Create this table via this code below:

slicer = GENERATESERIES(1,DISTINCTCOUNT(Contracts[ContractID]))

Then you need to create a measure named slicer for your visual.

slicer = IF ( [Contracts (by initial risk date)] >= MIN ( slicer[Value] ) && [Contracts (by initial risk date)] < = MAX ( 'slicer'[Value] ), 1, 0 )

Let it in filter pane on this visual and configure it show items is 1. Pbix file in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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

Re: Slicer for a DISTINCTCOUNT metric

pedroccamaraDBI — Tue, 05 Apr 2022 10:10:53 GMT

Hello v-chenwuz-msft
Fantastic idea! It's working.
I had to change that Generateseries measure,
GENERATESERIES(1,MAX(Contracts[ContractID]))

and also took out the plus (+) sign.
One last question, from what i've seen and read, wouldn't it be better with SELECTEDVALUE?
Just asking because i wouldn't know how to do it

Re: Slicer for a DISTINCTCOUNT metric

v-chenwuz-msft — Wed, 06 Apr 2022 05:19:16 GMT

Hi pedroccamaraDBI,

I apologize as I made some mistakes.

Change "+" -> "=".

Of course SELECTEDVALUE() works too, but you can only select one value instead of a range. If COUNTROWS(VALUES(slicer[Value])) > 1, then SELECTEDVALUE will get blank.

if(SELECTEDVALUE(slicer[Value]) = [Contracts (by initial risk date)],1,0)

Best Regards

Community Support Team _ chenwu zhu

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

Re: Slicer for a DISTINCTCOUNT metric

pedroccamaraDBI — Wed, 06 Apr 2022 19:12:13 GMT

Thank you so much v-chenwuz-msft
You've been most helpfull
Best regards

Re: Slicer for a DISTINCTCOUNT metric

pedroccamaraDBI — Fri, 08 Apr 2022 14:47:27 GMT

Hello v-chenwuz-msft
Thank you very much for your help
It works perfectly