count unique values in two tables with date slicer

[Anonymous]

I have two identical tables (PP1 and PP2) - there is a relationship on PP1 on 'start date' so that it can be used with the date slicer and a relationship on PP2 with the 'end date' so that it can be used with the date slicer.

My issue is that I need a distinct count of 'App ref' where the dates fall between the slicer dates - to include all of those with a 'start date' between those dates and all of those with an 'end date' between those dates but so that it does not count duplicates.

I've thought about adding both counts together and then creating a measure for 'App ref' where 'start date' and 'end date' are only within the slicer boundaries (i.e: this would count the duplicates) and then minus this from the original count of all, but I can't even seem to get my head around how that would work because the date range is dictated by a slicer not a set range.

There's probably a simple explanation to this, but I can't seem to see the wood for the trees. Please help 

[Anonymous]

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

A table named slicer should be created via Slicer = CALENDAR(min('PP1'[Creation Date]),MAX('PP2'[Completion Date])). And no relationship created between these table. This step is uesd to remove filter on table pp1 and pp2. We use the slicer to send parameters to the final measure rather than the filter which comes from relationship.

final Measure =
VAR _pp1 =
    CALCULATETABLE (
        VALUES ( PP1[App Ref] ),
        FILTER ( 'PP1', [Creation Date] IN VALUES ( Slicer[Date] ) )
    )
VAR _pp2 =
    CALCULATETABLE (
        VALUES ( PP2[App Ref] ),
        FILTER ( 'PP2', [Completion Date] IN VALUES ( Slicer[Date] ) )
    )
VAR _duplicates =
    COUNTROWS ( EXCEPT ( _pp1, _pp2 ) )
RETURN
    COUNTROWS ( _pp2 ) + _duplicates

Pbix 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.

[Anonymous]

Amazing! Thank-you so much.

I had to do some additional tweaks to make this bespoke to my dashboard, but this works perfectly. 

[AllisonKennedy]

@Anonymous  Why do you have two fact tables? (the PP1 and PP2)? Usually you would have two date dimension tables instead or even one date table with NO relationship to PP table and use a DAX approximate lookup: 

https://excelwithallison.blogspot.com/2020/06/dax-approximate-lookup.html 

[vojtechsima]

Hi, @Anonymous
Something like this?

Measure = 

var PP1_ = DISTINCTCOUNT(PP1[StartDate])
var PP2_ = DISTINCTCOUNT(PP2[EndDate])
return PP1_+PP2_

[Anonymous]

so my problem is that I need to count the distinct number of App Refs in these tables.

PP1 has 100 App Refs with a start date between the slicer dates, PP2 has 150 App Refs with an end date between the slicer dates, but 75 App Refs are counted in both tables. Adding PP1 and PP2 together gives me 250, but really my answer should be 175.

[vojtechsima]

Hi, @Anonymous 
Could you please share a sample dataset and desired Count, please?
(Copyable table)

[Anonymous]

shared above - not sure why it didn't let me reply directly.