Creating a Calculated Column Based on SELECTEDVALUE

[anaida]

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:

1. Using VAR to hold the output of SELECTEDVALUE in the creation of the calculated column
2. 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.

[bcdobbs]

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.

[anaida]

Is there any other way of achieving the desired result I'm looking for?

[bcdobbs]

Can you share your simplified pbix? I think I have a solution but need to play with it.

[anaida]

Sure thing, I've added it to dropbox here: https://www.dropbox.com/s/tk585qq3jy6nnzw/Lender%20Grouping%20Testing.pbix?dl=0

Let me know if I have to share it in some other way.

[bcdobbs]

See what you make of this:
Possible Solution 

I'll write up some explanation in a moment!

[bcdobbs · Accepted solution]

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"

[mellim]

Hi Ben, your solution worked very well in a similar use case I had. Thank you so much for contributing this.

[anaida]

This will certainly require quite a bit of effort to implement for all the different visuals that are present in the full report, but I can attest that it does work! Thank you.

[bcdobbs]

Do you have lots of different measures that would need the same treatment?

If so it might be worth seeing if you can use a calculation group to enable the logic to be reused easily.