Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Create an indicator based on a parameter

Hello,

 

I'm a relatively new PBI user for context.  I'm trying to ultimately create a measure that is equal to 1 for "include" and 0 for "exclude" that I can add as a filter to my page so that only certain rows are included in my visuals.  

 

The criteria for "include" are as follows:

a row should be "included" if...

 

Stts Cde is closed, and Dev Period <= some parameter that the user defines     (2 conditions)

 

OR

 

Stts Cde is open, and Dev Period = some parameter that the user defines          (2 conditions)

 

 

I was trying a calculated column, but I'm realizing that this does not work with the parameter.  However, I cannot use a measure either because I need it evaluated by row.  Does anyone have any tips?

 

This is the DAX for the calculated column that I have so far:

 

Calc_Filter =
    IF(Loss_Monitoring_Tool_Full_Data[Stts Cde] = "closed"
     && Loss_Monitoring_Tool_Full_Data[Dev Period] <= [PARAMETER]
     ||
     Loss_Monitoring_Tool_Full_Data[Stts Cde] = "open"
    && Loss_Monitoring_Tool_Full_Data[Dev Period] = [PARAMETER], 1, 0)
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous 

    You can transform it to measure

    Calc_Filter =
VAR a =
    COUNTROWS (
        FILTER (
            Loss_Monitoring_Tool_Full_Data,
            OR (
                [Stts Cde] = "closed"
                    && Loss_Monitoring_Tool_Full_Data[Dev Period] <= [PARAMETER],
                Loss_Monitoring_Tool_Full_Data[Stts Cde] = "open"
                    && Loss_Monitoring_Tool_Full_Data[Dev Period] = [PARAMETER]
            )
        )
    )
RETURN
    IF ( a > 0, 1, 0 )

    Then put the measure to the visual filter.

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Stts Cde is a column that is either "open" or "closed"

     

    Dev Period is an integer column with values 0 through 65

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous 

    You can transform it to measure

    Calc_Filter =
VAR a =
    COUNTROWS (
        FILTER (
            Loss_Monitoring_Tool_Full_Data,
            OR (
                [Stts Cde] = "closed"
                    && Loss_Monitoring_Tool_Full_Data[Dev Period] <= [PARAMETER],
                Loss_Monitoring_Tool_Full_Data[Stts Cde] = "open"
                    && Loss_Monitoring_Tool_Full_Data[Dev Period] = [PARAMETER]
            )
        )
    )
RETURN
    IF ( a > 0, 1, 0 )

    Then put the measure to the visual filter.

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you that worked!