cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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.

 

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 , 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Champion
Community Champion

@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

 

 




Lima - Peru

View solution in original post

6 REPLIES 6
Community Champion
Community Champion

@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

 

 




Lima - Peru

View solution in original post

Community Champion
Community Champion

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

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.

 

min.png

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




Lima - Peru

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

 

Community Champion
Community Champion

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.

 




Lima - Peru

Thanks @Vvelarde for your inputs.

 

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

 

Please accept my million thanks Smiley Happy

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors