Forum Discussion
Optimization on Many to Many to Many
Here's what you have to do.
1. Connect the ScenarioGroup table to Lane Masterdata as you have: ScenarioGroup[ScenarioGroup] 1 - * 'Lane Masterdata'[ScenarioGroup] where filtering is one-way from ScenarioGroup to 'Lane Masterdata'.
2. Connect Lane Masterdata to Bids: Lane Masterdata[LaneID] * - * Bids[LaneID] where filtering is one-way from 'Lane Masterdata' to Bids. Hide 'Lane Masterdata' (that's the best thing to do). You should only see ScenarioGroup and Bids. In ScenarioGroup you should only see the columns: Group, Scenario. In Bids you should only see: LSP. All other columns should be hidden.
3. Create 3 measures. In Bids create [Total BID]
Total BID = SUM( Bids[BID] )In ScenarioGroup create [Minimum Total BID]
Minimum Total BID = var __shouldCalc = HASONEVALUE( ScenarioGroup[ScenarioGroup] ) var __result = if( __shouldCalc, MINX( ALLSELECTED( Bids[LSP] ), [Total BID] ) ) return __resultand [Minimum LSP]
Minimum LSP = var __minTotalBid = [Minimum Total BID] var __result = CONCATENATEX( FILTER( ALLSELECTED( Bids[LSP] ), [Total BID] = __minTotalBid ), Bids[LSP], ",", Bids[LSP], ASC ) return __resultThen, in your matrix, you can remove LSP from columns (leave the rest) and drop the measures in there. You'll see they do what you want. Also, you should have a slicer in the canvass for the LSP field. Play with it to see that the measures do indeed do what you wanted.
Best
D
Here's what you have to do.
1. Connect the ScenarioGroup table to Lane Masterdata as you have: ScenarioGroup[ScenarioGroup] 1 - * 'Lane Masterdata'[ScenarioGroup] where filtering is one-way from ScenarioGroup to 'Lane Masterdata'.
2. Connect Lane Masterdata to Bids: Lane Masterdata[LaneID] * - * Bids[LaneID] where filtering is one-way from 'Lane Masterdata' to Bids. Hide 'Lane Masterdata' (that's the best thing to do). You should only see ScenarioGroup and Bids. In ScenarioGroup you should only see the columns: Group, Scenario. In Bids you should only see: LSP. All other columns should be hidden.
3. Create 3 measures. In Bids create [Total BID]
Total BID = SUM( Bids[BID] )In ScenarioGroup create [Minimum Total BID]
Minimum Total BID =
var __shouldCalc =
HASONEVALUE( ScenarioGroup[ScenarioGroup] )
var __result =
if( __shouldCalc,
MINX(
ALLSELECTED( Bids[LSP] ),
[Total BID]
)
)
return
__resultand [Minimum LSP]
Minimum LSP =
var __minTotalBid = [Minimum Total BID]
var __result =
CONCATENATEX(
FILTER(
ALLSELECTED( Bids[LSP] ),
[Total BID] = __minTotalBid
),
Bids[LSP],
",",
Bids[LSP],
ASC
)
return
__result
Then, in your matrix, you can remove LSP from columns (leave the rest) and drop the measures in there. You'll see they do what you want. Also, you should have a slicer in the canvass for the LSP field. Play with it to see that the measures do indeed do what you wanted.
Best
D
Like, many thanks, this is awesome.. Would you care to venture an explanation of [Minimum Total BID] and the use of ALLSELECTED within MINX?
ALLSELECTED(...) is a function that gives you access to all the values (of a column) that your visual is currently operating on. It's used for purely visual calculations and a measure that uses it should never be used in a measure that contains an iterator. It's the most complex function in all DAX and should be used WITH UTMOST CARE (if you want to know more about its complexity, please read articles about it on www.sqlbi.com). Since you want to get the minimum of BIDs across all the visible BIDs in the current visual (not only the ones in the current context), you have to use this function.
Best
D