Forum Discussion
Counting Values in a Joined Table
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.
you need to just use COUNTROWS functions
measure =
COUNTROWS( 'your'table)+0
6 Replies
you need to just use COUNTROWS functions
measure =
COUNTROWS( 'your'table)+0Ahmedx 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.
Thank you Ashish_Mathur that works well.
