cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## 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

3 ACCEPTED SOLUTIONS
Helper I

Thanks!  Yes, that worked great!

Super User

Don't forget to mark it as a solution.

Helper I

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 10
Helper I

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

Helper I

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.

Super User

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

Helper I

Thanks!  Yes, that worked great!

Super User

Don't forget to mark it as a solution.

Helper I

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?

Super User

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

Helper I

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?

Solution Sage

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.

Solution Sage

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors