cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
andrewyappip
Regular Visitor

How to make what-if parameters filter on row-level granularity?

Hi all,

 

Objective:

I want to be able to use a What-If Parameter (or any interactive control) to filter out rows that satisfy a certain condition.

 

Situation:

I have a table called "Data" where each row is a project represented by a Project ID. In the raw data, there is a field called "Days Since Last Update". I want to create an interactive way to filter out rows where "Days Since Last Update" > [Some Parameter Value].

 

I was able to make this work for a table visual with the following formula:

Is Days Since Last Update >eq Threshold? = IF(SUM(Data[Days Since Last Update]) >= 'PARAMETER - Days Since Last Update'[PARAMETER - Days Since Last Update Value],1,0)
 
Problem:
This works for the table because it has [Project ID] as one of its fields. However, it does not work for other visuals in my dashboard do not use the Project ID directly. Note that the formula uses a SUM of the Days Since Last Update, which is not really what I want, but I am forced to do this since calculated columns are not an option. Any advice on how to proceed?
 
 
In the workbook, play around with the parameter. It filters the table but does not change the charts.
 
Thanks,
Andrew
 
1 ACCEPTED SOLUTION

Accepted Solutions
Super User I
Super User I

Re: How to make what-if parameters filter on row-level granularity?

Hi @andrewyappip ,

 

I went through the pbix uploaded by you.

 

The approach that one should take is to create measures with the data filtered on conditions including paramters.

 

So in your case of the bar graph showing the Delayed and Ontime values, we need to create separate measures for each of them filtering the records from the sheet1 table based on the value in the parameter. 

 

The measure goes like this.

 

DelayedTotal =
CALCULATE (
    SUM ( [Delayed] ),
    FILTER (
        Sheet1,
        Sheet1[Days SInce Last Update]
            > VALUES ( Param[Days Since Last Update Threshold] )
    )
)

 

We sum the Delayed column values of rows that satisfy the condition DaysSinceLastUpdated column is greater than the Value of the parameter DaysSinceLastUpdateThreshhold.

 

By this approach it does not matter whether Project Id is included in any visual.

 

I have attached the updated pbix for your reference.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

5 REPLIES 5
Super User I
Super User I

Re: How to make what-if parameters filter on row-level granularity?

Hi @andrewyappip ,

 

Can you please post sample data and output expected in Google or OneDrive and share the link here to formulate a solution.

 

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
andrewyappip
Regular Visitor

Re: How to make what-if parameters filter on row-level granularity?

Hi CheenuSing,

 

I just updated the original post with 2 links: one for the spreadsheet data and one for the PBIX file.

 

Thanks,

Andrew

Super User I
Super User I

Re: How to make what-if parameters filter on row-level granularity?

Hi @andrewyappip ,

 

I went through the pbix uploaded by you.

 

The approach that one should take is to create measures with the data filtered on conditions including paramters.

 

So in your case of the bar graph showing the Delayed and Ontime values, we need to create separate measures for each of them filtering the records from the sheet1 table based on the value in the parameter. 

 

The measure goes like this.

 

DelayedTotal =
CALCULATE (
    SUM ( [Delayed] ),
    FILTER (
        Sheet1,
        Sheet1[Days SInce Last Update]
            > VALUES ( Param[Days Since Last Update Threshold] )
    )
)

 

We sum the Delayed column values of rows that satisfy the condition DaysSinceLastUpdated column is greater than the Value of the parameter DaysSinceLastUpdateThreshhold.

 

By this approach it does not matter whether Project Id is included in any visual.

 

I have attached the updated pbix for your reference.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

andrewyappip
Regular Visitor

Re: How to make what-if parameters filter on row-level granularity?

Hi CheenuSing,

 

Thank you -- this works perfectly! Just wondering if you could give any additional explanation as to why we have to do this -- it seems counter-intuitive (and tedious) to have to create a CALCULATE() function for each measure. My first instinct was to create a boolean column and create a visual/page filter on that, but Power BI won't let me.

 

Cheers,

Andrew

Highlighted
Super User I
Super User I

Re: How to make what-if parameters filter on row-level granularity?

Hi   @andrewyappip ,

 

Glad to note it worked for you.

 

To answer your question,  calculated columns irrespective of the type are created at the refresh time and can not be changed based on the slicer or parameter selection dynamically.  This is a limitation in Power BI.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors