Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Workaround for Dynamic Column calculation based on Slicer Selection

I have a simple column of values and I want to use a calculated column to determine if those values PASS or FAIL. However, I want to be able to change the minimum pass value and maximum fail value by using a slicer. When I put fixed numbers in the formula, the column values are shown appropriately; but, it has to be changed by editing the formula if I want to raise or lower minimum and maximum pass/fail values. I want to be able to change it using a slicer. 

WORKING:

 

Not working:

Result = IF('t1'[Column1]>='Pass parameter'[Pass parameter Value],"PASS",IF('t1'[Column1]<='FAIL parameter'[FAIL parameter Value], "FAIL", "UNDETERMINED"))

 

I read a lot that PowerBI isn't capable of dynamically caluculating a column. I realize that PowerBI might not be capable of doing EXACTLY what I want here, but there has to be some sort of workaround that allows me to change the column values with a slicer.

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Anonymous 

    First, you must know that calculated column and calculate table can't be affected by any slicer. 
    Notice:
    1. Calculation column/table not support dynamic changed based on filter or slicer.
    2. measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
    here is reference:
    https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
    Second, you could use a measure instead of it by this formula

    Measure Result = IF(SUM(t1[Column1])>=[Pass parameter Value],"PASS",IF(SUM(t1[Column1])<=[FAIL parameter Value],"FAIL","UNDETERMINED"))

    Result:

    By the way: You need to set Column1 is "Don't Summarize"

     

    Best Regards,

    Lin

     

     

     

1 Reply

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    hi, Anonymous 

    First, you must know that calculated column and calculate table can't be affected by any slicer. 
    Notice:
    1. Calculation column/table not support dynamic changed based on filter or slicer.
    2. measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
    here is reference:
    https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
    Second, you could use a measure instead of it by this formula

    Measure Result = IF(SUM(t1[Column1])>=[Pass parameter Value],"PASS",IF(SUM(t1[Column1])<=[FAIL parameter Value],"FAIL","UNDETERMINED"))

    Result:

    By the way: You need to set Column1 is "Don't Summarize"

     

    Best Regards,

    Lin