DISTINCTCOUNT per category, using relation

[grggmrtn]

Hey everyone - having a bit of a problem getting the data I need. Here's my data:

PersonID	DateRegistered	StartDate
1	01-01-2020	01-01-2020
1	02-01-2020	03-02-2020
1	03-02-2020	01-01-2020
2	05-05-2020	08-12-2019
2	06-06-2020	01-01-2020
2	07-06-2020	03-05-2020
3	12-07-2020	07-10-2020
3	14-10-2020	01-01-2020
4	16-10-2020	01-01-2020

This table is related to my date table on DateRegistered using the date table, but I also have an inactive relation on StartDate to the date table, because I need to find out how many unique StartDate there are per month.

this is the DAX I'm using now:

Antal indskrivninger = 
CALCULATE(
    DISTINCTCOUNT('table'[StartDato]),
    USERELATIONSHIP('table'[StartDato],Dato[Dato])
    )

and it is giving me of course, a unique count of StartDate per month. So the result for the above data is 5.

BUT

What I NEED is for it to give me unique number of StartDate per PersonID - so the result for above should be:

PersonID	Number of DistinctStartDato
1	2
2	3
3	2
4	1

and it should show me a total of 8, not 5.

Any idea how I can modify my DAX to give me the results I need?

[Fowmy · Accepted solution]

@grggmrtn 

Use your measure and modify as follows:

Antal indskrivninger = 

SUMX(
    SUMMARIZE(Table,Table[PersonID],Table[StartDate]),
    CALCULATE(
        DISTINCTCOUNT('Table'[StartDate]),
        USERELATIONSHIP(Table[StartDate],Dato[Date])
    )
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[grggmrtn]

Beautiful @Fowmy - thanks!