Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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
 
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi CheenuSing,

       

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

       

      Thanks,

      Andrew

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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