Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
anaida
Regular Visitor

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:

 

LendersShellTableImage.PNGLendersTableImage.PNG

 

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:

 

SelectedLenderMeasureCalculation.PNG

 

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"

 

GroupedColumnCalculationImage.PNG

 

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.

 

outputGraphics.PNG

 

I have also attached an image demonstrating the desired result below:

 

desiredResult.PNG

 

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.

 

 

1 ACCEPTED SOLUTION
bcdobbs
Community Champion
Community Champion

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:

bcdobbs_0-1645215832605.png

 

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"

 

bcdobbs_1-1645215970227.png

 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

View solution in original post

10 REPLIES 10
bcdobbs
Community Champion
Community Champion

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.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

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

bcdobbs
Community Champion
Community Champion

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



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

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
Community Champion
Community Champion

See what you make of this:
Possible Solution 

I'll write up some explanation in a moment!



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
bcdobbs
Community Champion
Community Champion

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:

bcdobbs_0-1645215832605.png

 

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"

 

bcdobbs_1-1645215970227.png

 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

I have a silimar problem I can't solve.

I need to select a specific Lender that must be showed with its name and its amount, but i don't want to "group" all the other Lenders in a single grouped amount, because I need to show all of them with their specific amounts, by "hiding" only their names.

Example, in case I select Lender2:

Screenshot_2.png   

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

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
Community Champion
Community Champion

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.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.