Forum Discussion
Creating a Calculated Column Based on SELECTEDVALUE
Hello PowerBI Community,
I am trying to create a calculated column based on the user's selection from a slicer.
Below are screenshots of my (simplified) data sources:
I set up a measure called 'Selected Lender' in the 'Lenders' table which tracks which Name the user has selected from the 'Lenders Shell'[Name] column via a slicer:
Then, I create a calculated column in the 'Lenders' table called 'Grouped' which is equal to the value in the 'Lenders'[Name] column if it matches the currently selected name, otherwise takes value "Other Lenders"
However, selecting a value via the slicer from 'Lenders Shell'[Name] does nothing to effect the value of the 'Lenders'[Grouped] column. The IF statement will always evaluate to false regardless of the selected value.
I have also attached an image demonstrating the desired result below:
The motivation behind this reasoning is that the user wishes to be able to select a Lender from a dropdown selection and anonymize all other lenders for the purposes of client presentations. The current functionality of using built-in column groupings is too cumbersome for them.
Some of the solutions I've already tried that did not work:
- Using VAR to hold the output of SELECTEDVALUE in the creation of the calculated column
- Directly referencing SELECTEDVALUE in the calculation
Some things I've found:
- The output of SELECTEDVALUE does render properly in a card visual when selecting a value from a slicer, but referencing the value in a calculation does not work. In particular: IF( SELECTEDVALUE('Lenders Shell'[Name]) == "Lender3", ... ) will always return false, regardless of whether "Lender3" has been selected from the slicer and is visible in the card visual.
1) Removed your existing inactive relationship and your calculated column.
2) Added a calculated table which lists each lender and adds an "Other Lenders" category:
Lender Grouped = UNION ( DISTINCT ( Lenders[Name] ), {"Other Lenders"} )3) Created a relationship to your main table (you need to force it to be 1 to many:
4) Created a basic total measure:
Lender Amount = SUM ( Lenders[Amount] )(This gets used in your normal ungrouped visual)
5) Created a measure to do the grouping:
Grouped Amount = VAR SelectedLenders = VALUES ( 'Lender Shell'[Name] ) VAR VisualLender = SELECTEDVALUE ( 'Lender Grouped'[Name] ) RETURN IF ( HASONEVALUE ('Lender Grouped'[Name]), SWITCH ( TRUE (), VisualLender IN SelectedLenders, [Lender Amount], VisualLender = "Other Lenders", CALCULATE ( [Lender Amount], NOT ('Lender Grouped'[Name] IN SelectedLenders ) ), BLANK() ), BLANK() )6) Swap both visuals over to use "Lender Grouped[Name]"
7) In the grouped visual replace the implicit measure with "Grouped Amount"
10 Replies
Hi,
I'm afraid calculated columns are only refreshed when the model is processed. Eg at data load. They don't ever respond dynamically to slicers.
