Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi, I am trying to accomplish something similar to what was posted in this post: Count column value based on multiple criteria in another table on 2/20/24. But my request is a little different. Below are some sample tables I am using to test this:
Data:
ID | Date |
1 | 11/12/2022 |
1 | 12/23/2022 |
1 | 5/15/2023 |
1 | 1/1/2024 |
2 | 11/25/2022 |
2 | 12/12/2022 |
Date ranges:
Start date | End date |
11/1/2022 | 10/31/2023 |
12/1/2022 | 11/30/2023 |
1/1/2023 | 12/31/2023 |
2/1/2023 | 1/31/2024 |
3/1/2023 | 2/29/2024 |
I'd like to count rows in "Data" that are between the dates in "Date ranges" and distinct rows for same based on "ID" in order to calculate a frequency. I am thinking to create measures for distinct rows, count of rows and average frequency for IDs for each date range ("Frequency" = "Rows" divided by "Distinct") as follows:
Start date | End date | Rows | Distinct | Frequency |
11/1/2022 | 10/31/2023 | 5 | 2 | 2.5 |
12/1/2022 | 11/30/2023 | 3 | 2 | 1.5 |
1/1/2023 | 12/31/2023 | 1 | 1 | 1 |
2/1/2023 | 1/31/2024 | 2 | 1 | 2 |
3/1/2023 | 2/29/2024 | 2 | 1 | 2 |
I tried using the code in this post: Count column value based on multiple criteria in another table on 2/20/24, modified for my data but I get an error in defining the variables, as follows:
I would appreciate some help in how to do this including any relationships between tables I need. Currently there are no relationships. John
Solved! Go to Solution.
Yes, @gmsamborn . That worked great including putting similar logic in my production report and adding additional slicers which work individually and in combination. Thanks! John
Yes, @gmsamborn . That worked great including putting similar logic in my production report and adding additional slicers which work individually and in combination. Thanks! John
Hi @gmsamborn What you helped me with was very useful and now in production. I now have a similar application to my earlier request you helped with but I want to add a slicer on a third field. Same as above except:
Data:
ID | Date | Slicer value |
1 | 11/12/2022 | A |
1 | 12/23/2022 | A |
1 | 5/15/2023 | A |
1 | 1/1/2024 | B |
2 | 11/25/2022 | B |
2 | 12/12/2022 | B |
Date ranges:
Start date | End date |
11/1/2022 | 10/31/2023 |
12/1/2022 | 11/30/2023 |
1/1/2023 | 12/31/2023 |
2/1/2023 | 1/31/2024 |
3/1/2023 | 2/29/2024 |
I'd like to calculate a frequency for IDs for each date range ("Frequency" = "Rows" divided by "Distinct"), but be able to select a value for the "Slicer valule" field as follows:
Slicer not selected | ||||
Start date | End date | Rows | Distinct | Frequency |
11/1/2022 | 10/31/2023 | 5 | 2 | 2.5 |
12/1/2022 | 11/30/2023 | 3 | 2 | 1.5 |
1/1/2023 | 12/31/2023 | 1 | 1 | 1 |
2/1/2023 | 1/31/2024 | 2 | 1 | 2 |
3/1/2023 | 2/29/2024 | 2 | 1 | 2 |
"A" selected in slicer | ||||
Start date | End date | Rows | Distinct | Frequency |
11/1/2022 | 10/31/2023 | 3 | 1 | 3 |
12/1/2022 | 11/30/2023 | 2 | 1 | 2 |
1/1/2023 | 12/31/2023 | 1 | 1 | 1 |
2/1/2023 | 1/31/2024 | 1 | 1 | 1 |
3/1/2023 | 2/29/2024 | 1 | 1 | 1 |
"B" selected in slicer | ||||
Start date | End date | Rows | Distinct | Frequency |
11/1/2022 | 10/31/2023 | 2 | 1 | 2 |
12/1/2022 | 11/30/2023 | 1 | 1 | 1 |
1/1/2023 | 12/31/2023 | 0 | 0 | 0 |
2/1/2023 | 1/31/2024 | 1 | 1 | 1 |
3/1/2023 | 2/29/2024 | 1 | 1 | 1 |
I've tried using the suggestion you provided above and adding filters on the slicer field but wasn't successful. Just a note that in my actual application, I want to add slicers on multiple fields but limited to one in this post to keep it simpler. I'm thinking that a solution for one slicer, I can adapt for multiple slicers.
Thanks.
Hi @jeben
Would these measures help?
_Distinct (inner) =
VAR _Slicer = ALLSELECTED( 'Data'[Slicer value] )
VAR _Count =
CALCULATE(
DISTINCTCOUNT( 'Data'[ID] ),
REMOVEFILTERS( 'Data' ),
'Data'[Date] >= SELECTEDVALUE( 'Date Ranges'[Start date] )
&& 'Data'[Date] <= SELECTEDVALUE( 'Date Ranges'[End date] )
&& 'Data'[Slicer value] IN _Slicer
)
RETURN
_Count
_Rows (inner) =
VAR _Slicer = ALLSELECTED( 'Data'[Slicer value] )
VAR _Count =
CALCULATE(
COUNTROWS( 'Data' ),
REMOVEFILTERS( 'Data' ),
'Data'[Date] >= SELECTEDVALUE( 'Date Ranges'[Start date] )
&& 'Data'[Date] <= SELECTEDVALUE( 'Date Ranges'[End date] )
&& 'Data'[Slicer value] IN _Slicer
)
RETURN
_Count
Thanks! Yes, that worked great!
I'd like to count rows in "Data" that are between the dates in "Date ranges" in order to calculate a frequency. I don't see how I can do this with 'Data'[Date] or 'Data'[ID] in my matrix. I need in my matrix a count of how many rows in 'Data' have a [Date] that is between the [Start date] and [End date] in 'Date ranges'. Maybe I don't understand what you are suggesting.
Or maybe this can't be done with Power BI and instead I need to prepare my data so I don't need to do this in Power BI?
Hi @jeben
I came up with my solution in a couple of steps.
First, I created these measures:
_Rows (inner) =
CALCULATE(
COUNTROWS( 'Data' ),
REMOVEFILTERS( 'Data' ),
'Data'[Date] >= SELECTEDVALUE( 'Date Ranges'[Start date] )
&& 'Data'[Date] <= SELECTEDVALUE( 'Date Ranges'[End date] )
)
_Distinct (inner) =
CALCULATE(
DISTINCTCOUNT( 'Data'[ID] ),
REMOVEFILTERS( 'Data' ),
'Data'[Date] >= SELECTEDVALUE( 'Date Ranges'[Start date] )
&& 'Data'[Date] <= SELECTEDVALUE( 'Date Ranges'[End date] )
)
Next, the easiest way to handle the Total line is like this.
Rows =
SUMX(
VALUES( 'Date Ranges'[Start date] ),
[_Rows (inner)]
)
Distinct =
SUMX(
VALUES( 'Date Ranges'[Start date] ),
[_Distinct (inner)]
)
and the final measure:
Frequency =
DIVIDE(
[Rows],
[Distinct]
)
The first 2 measures could be hidden.
Let me know if you have any questions.
Rows in date ranges - test.pbix
Thanks! That solved for the error. Now however, I am not able to visualize the data via a matrix. Below is a screenshot. Would it help if I send your my pibx file?
The reason for your issue is that you have used the wrong fields in your matrix. Your measure is written to expect a filter context of 'Data'[Date] and 'Data'[ID]. You would need to use those fields in your matrix.
If you have a table relationship between 'Date Ranges' and 'Data' you could potentially use End Date like you have used here.
The code you have written looks like its for a "Calculated Column" instead of a measure. A measure is run against whatever context it finds itself in. A Calculated column runs row by row in the data table you are making the column in.
If this was intended to be a measure where you will have a visual that will show your row by row data, wrap your 'Data'[Date] and 'Data'[ID] in the function SELECTEDVALUE(). Without this, DAX doesn't understand how to aggregate the column you have provided. While you understand you are going to be using the measure in a context where there is only going to be 1 single value, DAX cannot assume this.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.