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"
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.
Is there any other way of achieving the desired result I'm looking for?
- bcdobbs4 years agoCommunity Champion
Can you share your simplified pbix? I think I have a solution but need to play with it.
- anaida4 years agoRegular Visitor
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.
- bcdobbs4 years agoCommunity Champion
See what you make of this:
Possible Solution
I'll write up some explanation in a moment!