Calculate frequency value based on multiple criteria in another table

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

Need Help

Anonymous
2 years ago
Thanks! Yes, that worked great!
gmsamborn
2 years ago
Your welcome, Anonymous . I'm glad it worked.

Don't forget to mark it as a solution.
Anonymous
2 years ago
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

10 Replies

Anonymous
Not applicable
2 years ago
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.
Anonymous
Not applicable
2 years ago
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?
- Anonymous
  Not applicable
  2 years ago
  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.
Anonymous
Not applicable
2 years ago
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?
- gmsamborn
  Super User
  2 years ago
  Hi Anonymous
  
  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
Anonymous
Not applicable
2 years ago
Thanks! Yes, that worked great!
- gmsamborn
  Super User
  2 years ago
  Your welcome, Anonymous . I'm glad it worked.
  
  Don't forget to mark it as a solution.

Anonymous

Not applicable

2 years ago

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.

gmsamborn

Super User

2 years ago

Hi Anonymous

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

Rows in date ranges - 2.pbix

Anonymous
Not applicable
2 years ago
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

Forum Discussion

Calculate frequency value based on multiple criteria in another table

10 Replies

Related content

Calculating Orders Frequency

Calculate frequency percentage

Calculate Average Order frequency

CALCULATE with multiple filters

Using Visual Calculation on a Calculated Group with Multiple Calculated Items