Slicer for a DISTINCTCOUNT metric

[pedroccamaraDBI]

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

[v-chenwuz-msft · Accepted solution]

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.

[v-chenwuz-msft · Accepted solution]

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.

[pedroccamaraDBI]

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

[v-chenwuz-msft · Accepted solution]

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.

[pedroccamaraDBI]

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

[pedroccamaraDBI]

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

[amitchandak]

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

[pedroccamaraDBI]

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