Forum Discussion
Creating a Calculated Column Based on SELECTEDVALUE
- 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"
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"
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: