Dynamic calculate Column value based on Slicer Selection
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 -
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.
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.
@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.
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
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.