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

[Anonymous]

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?

Files:
- Sample Data: https://drive.google.com/file/d/1fYG1dRtkthvLhxMTDXxbx1x_VmoVxf0Y/view?usp=sharing

- Sample Workbook: https://drive.google.com/file/d/1TqT_xKZg3s7sSyZJr4yrQPtt-iW4mU8I/view?usp=sharing

In the workbook, play around with the parameter. It filters the table but does not change the charts.

Thanks,

Andrew

[CheenuSing]

Hi @Anonymous ,

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

Cheers

CheenuSing

[Anonymous]

Hi CheenuSing,

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

Thanks,

Andrew

[CheenuSing · Accepted solution]

Hi @Anonymous ,

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

[Anonymous]

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

[CheenuSing]

Hi   @Anonymous ,

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