Forum Discussion

anaida's avatar
anaida
Regular Visitor
4 years ago
Solved

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 ...
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    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"