Dynamic calculate Column value based on Slicer Selection

[BenWei]

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.

Here are the scenario - 

I have

    a slicer, and its data table (Table 1) has two columns (Index, Name). 

    a table, and its data table (Table 2) has thre columns (Id, Name, Applicabilities). The applicabilities is a 1 and 0 based 200 characters long string. 

Backgroun: 

    The Table 1's index match the Table 2's applicabilities's characters' position.

What I want to achieve is following.

    After user selected an item from the slicer, Microsoft BI takes this selected index (name it as P), and show the Table2 rows if the character at the position P of the current row's applicabilites string equals to

What I had tried so far.

    1. I created a measure (SelectedIndex =  MIN(TABLE[INDEX))  in Table 1 to get the selected Index

    2. Create a column (Applicable = MID( [APPLICABILITIES], [SelectedIndex], 1) ) in Table 2 to find the character from Table 2's applicabiliteis string based on the measure (SelectedIndex)

However, the SelectedIndex always return smallest index of the Table 1's entire dataset.

Any help would be very much appreciated , 

[ankitpatira]

@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]

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]

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.

[Vvelarde]

Hi @ankitpatira

I  dont understand when you say that a measure with Min Function always return the minimum value of a table regardless the selection.

I tested a slicer with indexes and select one of this. The measure with min(Index) return the selected index changing any time with the selection.

Maybe i missunderstood your explanation. Thanks for your comments.

 edit: In a calculated column Min return always the minimum value but in a measure changes with the context.

[Vvelarde · Accepted solution]

@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

[BenWei]

Thanks @Vvelarde for your inputs.

After I tried your first proposed solution, it worked magically. 

Please accept my million thanks