Forum Discussion

grggmrtn's avatar
grggmrtn
Icon for Post Patron rankPost Patron
5 years ago
Solved

DISTINCTCOUNT per category, using relation

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

PersonIDDateRegisteredStartDate
101-01-202001-01-2020
102-01-202003-02-2020
103-02-202001-01-2020
205-05-202008-12-2019
206-06-202001-01-2020
207-06-202003-05-2020
312-07-202007-10-2020
314-10-202001-01-2020
416-10-202001-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:

PersonIDNumber of DistinctStartDato
12
23
32
41

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?

Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    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




2 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    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