Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
could anyone give me an idea how to count rows in a joined table please?
I have a table of people (all unique records) joined to a table of dates (each person/date combination is unique) with a one to many relationship. In the people table I'd like to create a DAX expression (DATE COUNT) which will count the number of dates for that person in the dates table.
People
PERSON | DATE COUNT |
1234_ABC | 3 |
1255_ABC | 1 |
1234_CDE | 6 |
1344_XYZ | 2 |
1499_MMM | 0 |
Dates
PERSON | DATE |
1234_ABC | 1/01/2020 |
1234_ABC | 2/01/2020 |
1234_ABC | 3/01/2020 |
1255_ABC | 1/01/2020 |
1234_CDE | 1/01/2020 |
1234_CDE | 2/01/2020 |
1234_CDE | 3/01/2020 |
1234_CDE | 4/01/2020 |
1234_CDE | 5/01/2020 |
1234_CDE | 6/01/2020 |
1344_XYZ | 1/01/2020 |
1344_XYZ | 2/01/2020 |
Thank you in advance for your help.
Solved! Go to Solution.
you need to just use COUNTROWS functions
measure =
@Ahmedx thank you so much for your solution. How could I change it to only count dates that are "VALID" according to another column?
So if the Dates table now looked like this:
PERSON | DATE | VALID |
1234_ABC | 1/01/2020 | VALID |
1234_ABC | 2/01/2020 | VALID |
1234_ABC | 3/01/2020 | INVALID |
1255_ABC | 1/01/2020 | VALID |
1234_CDE | 1/01/2020 | VALID |
1234_CDE | 2/01/2020 | VALID |
1234_CDE | 3/01/2020 | INVALID |
1234_CDE | 4/01/2020 | INVALID |
1234_CDE | 5/01/2020 | INVALID |
1234_CDE | 6/01/2020 | INVALID |
1344_XYZ | 1/01/2020 | VALID |
1344_XYZ | 2/01/2020 | VALID |
And the result should now look like this;
PERSON | DATE COUNT |
1234_ABC | 2 |
1255_ABC | 1 |
1234_CDE | 2 |
1344_XYZ | 2 |
1499_MMM | 0 |
Hi,
Write this measure
Measure = calculate(countrows(Dates),Dates[Valid]="Valid")
Hope this helps.
You are welcome.
you need to just use COUNTROWS functions
measure =
User | Count |
---|---|
91 | |
74 | |
67 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
72 | |
60 | |
59 |