Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Optimization on Many to Many to Many

 

'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.

  • Anonymous's avatar
    Anonymous
    6 years ago

    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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi

       

      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!

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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