Forum Discussion
Advocate IICount column value based on multiple criteria in another table
Hi all,
I am hoping someone can help. I am trying to count the number of 'ID' in table 1 where the 'Date' is >= 'Start' & <= 'End' (from Table 2). I have tried the DAX below, but this appears to just count all rows (instead of ID) found between the 'Start' & 'End' date.
Count = CALCULATE (
DISTINCTCOUNT ( 'Table2'[ID] ),
FILTER ('Table2',
[Date] >= 'Table2'[Start Date]
&& [Date] <= 'Table2'[End Date]
&& Table2[ID] = [ID]
))
I am attempting to generate the results shown in the 'Count' column below.
Table1
| ID | Date | Count |
| 1 | 04/01/24 | 2 |
| 2 | 30/01/24 | 1 |
| 3 | 30/01/24 | 0 |
| 4 | 10/01/24 | 1 |
Table2
| ID | Start | End |
| 1 | 01/01/24 | 01/01/25 |
| 1 | 05/01/24 | 05/01/25 |
| 1 | 04/01/24 | 04/01/24 |
| 2 | 01/01/22 | 01/01/23 |
| 2 | 12/12/23 | 05/05/24 |
| 3 | 01/01/20 | 01/01/21 |
| 4 | 01/01/24 | 10/01/24 |
Any help eternally appreciated!
CM
Hey ClemFandango ,
as I understand, you want the result as calculated column, is that right?
Try the following calculated column:Count = VAR _DateCurrentRow = Table1[Date] VAR _IDCurrentRow = Table1[ID] VAR _FilterTable2 = FILTER( Table2, [Start] <= _DateCurrentRow && [End] >= _DateCurrentRow && Table2[ID] = _IDCurrentRow) VAR _Result = COUNTROWS( _FilterTable2 ) RETURN _ResultIf you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
Best regards
Denis
Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovicHi, ClemFandango
Maybe you can use the follow DAX :Count = VAR _count=CALCULATE ( COUNT ( 'Table2'[ID] ), FILTER ('Table2', RELATED(Table1[Date]) >= 'Table2'[Start] && RELATED(Table1[Date]) <= 'Table2'[End] && Table2[ID] = [ID] )) RETURN IF(_count> 0, _count, 0)Here is my preview:
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- selimovd
Most Valuable Professional
Hey ClemFandango ,
as I understand, you want the result as calculated column, is that right?
Try the following calculated column:Count = VAR _DateCurrentRow = Table1[Date] VAR _IDCurrentRow = Table1[ID] VAR _FilterTable2 = FILTER( Table2, [Start] <= _DateCurrentRow && [End] >= _DateCurrentRow && Table2[ID] = _IDCurrentRow) VAR _Result = COUNTROWS( _FilterTable2 ) RETURN _ResultIf you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
Best regards
Denis
Blog: WhatTheFact.bi
Follow me: twitter.com/DenSelimovic
ClemFandangoThis is amazing - thanks so much
Hi, ClemFandango
Maybe you can use the follow DAX :Count = VAR _count=CALCULATE ( COUNT ( 'Table2'[ID] ), FILTER ('Table2', RELATED(Table1[Date]) >= 'Table2'[Start] && RELATED(Table1[Date]) <= 'Table2'[End] && Table2[ID] = [ID] )) RETURN IF(_count> 0, _count, 0)Here is my preview:
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.