Forum Discussion

BenWei's avatar
BenWei
Frequent Visitor
9 years ago
Solved

Dynamic calculate Column value based on Slicer Selection

Hi Everyone   I just started using Microsoft Power BI recently, and this got stucked for few hours already. Hope someone could help me on this. This might be a simple question to the gurus.   Her...
  • Vvelarde's avatar
    Vvelarde
    9 years ago

    BenWei

     

    try with this:

     

    A measure:

     

    Applicable = if(HASONEVALUE(Table2[Applicabilities]),if(MID(VALUES(Table2[Applicabilities]),[SelectedIndex],1)=FORMAT([SelectedIndex],BLANK()),1,0))

     

    In visual filter Put Applicable is not 0

     

     

ankitpatira's avatar
ankitpatira
Community Champion
9 years ago

Vvelarde Use of MIN() function is wrong there as it is always going to return minimum value from the table regardless of selection. You should use combination of HASONEVALUE and FIRSTNONBLANK functions. HASONEVALUE will let you check if the filter has been applied and is down to just one value, it will return TRUE or FALSE. FIRSTNONBLANK will return the first value. See this example. It is for different purpose but you can achieve your desired output using these two fuctions.

BenWei's avatar
BenWei
Frequent Visitor
9 years ago

Thanks ankitpatira and Vvelarde for your replies

 

I had tried HASONEVALUE in column calculation, and it still doesn't do what I wanted. Please let me know if I did anything completely wrong. I just did a simple test to see if the function HASONEVALUE will return the same value in both Measure and Column calculation. 

 

The following are the testing scenario

 

Used the same calculation for each type (Measure and Column), After I select a name from the Table1's slicer, the following are the result that I got

 

Column_Calculation = IF(HASONEVALUE(Table1[INDEX]), 1, 0)       ==> This returns 0

Measure_Calculation = IF(HASONEVALUE(Table1[INDEX]), 1, 0)      ==> This returns 1

 

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    9 years ago

    In a column there is no selection to Index so result is 0 value

    A measure is working with the context so if you select a Index the result is 1 value selected.

     

    Other sample if you put Min(Index) in a calculated column the result is always thin min value of all the table. But if you put in a measure this work with the context and if you use a slice with indexes..the min is the selected value.