Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
jeben
Helper I
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:

IDDate
111/12/2022
112/23/2022
15/15/2023
11/1/2024
211/25/2022
212/12/2022

 

Date ranges:

Start dateEnd date
11/1/202210/31/2023
12/1/202211/30/2023
1/1/202312/31/2023
2/1/20231/31/2024
3/1/20232/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 dateEnd dateRowsDistinctFrequency
11/1/202210/31/2023522.5
12/1/202211/30/2023321.5
1/1/202312/31/2023111
2/1/20231/31/2024212
3/1/20232/29/202421

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:

jeben_0-1709245246173.png

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

Thanks!  Yes, that worked great!  

View solution in original post

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

 

Don't forget to mark it as a solution.

View solution in original post

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

View solution in original post

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

jeben
Helper I
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: 

IDDateSlicer value
111/12/2022A
112/23/2022A
15/15/2023A
11/1/2024B
211/25/2022B
212/12/2022

B

 

Date ranges:

Start dateEnd date
11/1/202210/31/2023
12/1/202211/30/2023
1/1/202312/31/2023
2/1/20231/31/2024
3/1/20232/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 dateEnd dateRowsDistinctFrequency
11/1/202210/31/2023522.5
12/1/202211/30/2023321.5
1/1/202312/31/2023111
2/1/20231/31/2024212
3/1/20232/29/2024212
     
"A" selected in slicer   
Start dateEnd dateRowsDistinctFrequency
11/1/202210/31/2023313
12/1/202211/30/2023212
1/1/202312/31/2023111
2/1/20231/31/2024111
3/1/20232/29/2024111
     
"B" selected in slicer   
Start dateEnd dateRowsDistinctFrequency
11/1/202210/31/2023212
12/1/202211/30/2023111
1/1/202312/31/2023000
2/1/20231/31/2024111
3/1/20232/29/2024111
     

 

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

 

Rows in date ranges - 2.pbix

 

 

jeben
Helper I
Helper I

Thanks!  Yes, that worked great!  

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

 

Don't forget to mark it as a solution.

jeben
Helper I
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?

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

 

jeben_1-1709247245785.png

jeben_2-1709247286618.png

 

 

 

RossEdwards
Solution Specialist
Solution Specialist

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
Solution Specialist
Solution Specialist

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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