Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
82 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |