Get distinct count of "ID's" when comparing a column with itself (based on Slicer selections)

[Anonymous]

Hi

I have a table which have 2 columns. "Month" Column (Jan-Dec) & "ID" column (contain duplicates). There are 2 month slicers , "Month1" slicer contains "Jan-June", "Month2" slicer contains "July - Dec". I want to create a measure. If I select "Feb" from Month1 slicer & "October" from Month2 slicer,

then Measure need to comprae "Feb" ID's with "October" ID's and gives distinct "count" of ID's which are not not matches (those ID's present in "Feb" month and are not present in "october" month ). 

Thank you in advance.

regards
Rajkumar

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

You could create a table as slicer.then create a measure.

Slicer = SUMMARIZE( SUMMARIZE('Table',[Date],"Date1",EOMONTH([Date],0),"Month",FORMAT([Date],"mmm")),[Date1],[Month])

Then create the measures.

comprae = //CALCULATE(COUNT([Id]),FILTER('Table',FORMAT([Date],"mmm") in ALLSELECTED(Slicer[Month])))
var _min=SUMMARIZE(FILTER('Table',EOMONTH([Date],0) = MIN('Slicer'[Date1])),[Id])
var _max=SUMMARIZE(FILTER(ALL('Table'),EOMONTH([Date],0) = MAX('Slicer'[Date1])),[Id])
return  COUNTROWS(_min)-COUNTROWS(_max)

distinct = 
var _min=SUMMARIZE(FILTER('Table',EOMONTH([Date],0) = MIN('Slicer'[Date1])),[Id])
var _max=SUMMARIZE(FILTER(ALL('Table'),EOMONTH([Date],0) = MAX('Slicer'[Date1])),[Id])
return  COUNTROWS( EXCEPT(_min,_max))

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft · Accepted solution]

Hi, @Anonymous ;

You could create a table as slicer.then create a measure.

Slicer = SUMMARIZE( SUMMARIZE('Table',[Date],"Date1",EOMONTH([Date],0),"Month",FORMAT([Date],"mmm")),[Date1],[Month])

Then create the measures.

comprae = //CALCULATE(COUNT([Id]),FILTER('Table',FORMAT([Date],"mmm") in ALLSELECTED(Slicer[Month])))
var _min=SUMMARIZE(FILTER('Table',EOMONTH([Date],0) = MIN('Slicer'[Date1])),[Id])
var _max=SUMMARIZE(FILTER(ALL('Table'),EOMONTH([Date],0) = MAX('Slicer'[Date1])),[Id])
return  COUNTROWS(_min)-COUNTROWS(_max)

distinct = 
var _min=SUMMARIZE(FILTER('Table',EOMONTH([Date],0) = MIN('Slicer'[Date1])),[Id])
var _max=SUMMARIZE(FILTER(ALL('Table'),EOMONTH([Date],0) = MAX('Slicer'[Date1])),[Id])
return  COUNTROWS( EXCEPT(_min,_max))

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

HI v-yalanwu-msf,

Thank you so much for reply. Except slicer selection everything is perfect.

In my data , I have month column (only month names, Jan-Dec) and ID Column (with duplicates). As per requirement , i need to create 2 month slicers. Slicer1 contains Jan-Jun , Slicer2 contains Jul-Dec. Requirement is , based on 2 slicers selection (Example: Slicer1: Feb, Slicer2: Oct) Measure have to compare February ID's with October ID's and give Distinct Count which are not matches (that means, ID's present in February month and are not present in October month)



v-yalanwu-msf : instead of one slicer, create measure based on 2 slicer selection.

Thank you in advance.