Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

Optimization on Many to Many to Many

henrikloev_0-1594650559916.png

 

'Lane Masterdata'

henrikloev_1-1594650804771.png

 

'Bids'

henrikloev_2-1594650856846.png

 

'Scenario_Group'

henrikloev_3-1594650892384.png

 

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

D

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

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
Anonymous
Not applicable

Hi

 

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

henrikloev_4-1594664568099.png

 

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

henrikloev_3-1594664557746.png

The corresponding desired output marked in yellow.

 

 

I hope this helps, or else please fire away.


Thanks!

 

 

Anonymous
Not applicable

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

D

Anonymous
Not applicable

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

Anonymous
Not applicable

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.