Calculate frequency value based on multiple criteria in another table

[jeben]

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

[jeben · Accepted solution]

Thanks!  Yes, that worked great!  

[gmsamborn · Accepted solution]

Your welcome, @jeben .  I'm glad it worked.

Don't forget to mark it as a solution.

[jeben · Accepted 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

[jeben]

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]

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

Rows in date ranges - 2.pbix

[jeben · Accepted solution]

Thanks!  Yes, that worked great!  

[gmsamborn · Accepted solution]

Your welcome, @jeben .  I'm glad it worked.

Don't forget to mark it as a solution.

[jeben]

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]

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

[jeben]

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?  

[RossEdwards]

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.

[RossEdwards]

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.