topic Re: Optimization on Many to Many to Many in DAX Commands and Tips

Optimization on Many to Many to Many

Anonymous — Mon, 13 Jul 2020 14:53:56 GMT

'Lane Masterdata'

'Bids'

'Scenario_Group'

The data model is given above. The labels for the keys are identical by name, ie. 'Bids' and 'Lane Masterdata' is joined by 'Bids'[LaneID] and 'Lane Masterdata'[LaneID].

The challenge is, in literal non-dax terms: For each record 'Scenarios_Group', find all related records (LaneID's) in 'Lane Masterdata'. Then find the unique 'Bids'[LSP] where the sum of all 'Bids'[BID] is the minimum.

Ie. For each scenario, for each group, find the collective corresponding minimum bid among all the collective bids of the LSPs.

I have solved this easily by the use of M, but I would like a more dynamic solution, where my users can have the result dependent on their filter selection.

Anything unclear, please feel free to ask away.

Br,

Henrik

Everything attached is a constructed example without meaningful reference.

Re: Optimization on Many to Many to Many

Anonymous — Mon, 13 Jul 2020 17:23:35 GMT

It would be good if you could post the expected result... What one needs is the input data and then the output. I see the input data but would like to see the outcome as well.

Thanks.

Best
D

Re: Optimization on Many to Many to Many

Anonymous — Mon, 13 Jul 2020 18:32:23 GMT

A sample output from 'Scenario_Group', two measures marked in green below,

And for your reference, please see the full "collective bids" list done in a matrix chart,

The corresponding desired output marked in yellow.

I hope this helps, or else please fire away.

Thanks!

Re: Optimization on Many to Many to Many

Anonymous — Tue, 14 Jul 2020 17:07:27 GMT

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 __result

and [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

Re: Optimization on Many to Many to Many

Anonymous — Mon, 20 Jul 2020 12:05:47 GMT

Like, many thanks, this is awesome.. Would you care to venture an explanation of [Minimum Total BID] and the use of ALLSELECTED within MINX?

Re: Optimization on Many to Many to Many

Anonymous — Tue, 21 Jul 2020 08:29:32 GMT

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