How to use Stage field from child table to slice amount in parent table in a many-to-many

[Anonymous]

I created a table (named: Snapshot 1) using Salesforce opportunity data where a snapshot is captured every 1st of each month and tagged every record with Snapshot Date (e.g. 4/1/2019, 5/1/2019, etc.). I then duplicated Snapshot 1 as Snapshot 2 and joined them using opportunity id field, many-to-many cardinality, single cross-filter direction, Snapshot 1 filters Snapshot 2.

I have 2 snapshot date slicers - one for Snapshot 1 and another one for Snapshot 2. In my testing, I set Snapshot 1 filter to 7/1/2019 and Snapshot 2 filter to 4/1/2019. I also filtered Snapshot 1 stage = Closed-Won. I then created a matrix with Opportunity Stage from Snapshot 2 in Rows and Opportunity Stage from Snapshot 1 in Columns. I then added the amount field from both snapshot tables to Values.

Here’s a screenshot of the resulting table. I’m getting the correct amounts from Snapshot 2 (since S1 filters S2). But in the Snapshot 1 amount column, I couldn’t split the amount (total won of 23.9M) among the stages from Snapshot 2. Basically, I want to know the composition of the 23.9M won deals in July based on what the stage was back in April.

Here’s my current measure: CALCULATE(SUM(‘Opportunity Snapshots 1’[Line Amount]))
How do I modify this so that the Snapshot 1 amount gets filtered by the Snapshot 2 stages?

Best regards,

Ferdinand

[Anonymous]

Before you start using many-to-many just please make sure YOU DO UNDERSTAND HOW THEY WORK and what the pitfalls are. Because trust me: THERE ARE MANY.

The best way to implement many-to-many is to have two tables that serve as dimensions and then a bridge table between them. This is the best design by far. Using many-to-many in Power BI is tricky and you're not even probably aware of all the gotchas.

Best

Darek

[Anonymous]

Thanks for the advice darlove.  The bridge approach was actually the first approach I tried where I created a table of unique ids and linked both files to it.  I wasn't able to get the desired outcome so I'm trying the many-to-many join, one way, this time.  Here's a representation of the result I'm trying to get.  I'm hoping I only need the correct dax measure.

Best regards,

Ferdinand

[Anonymous]

Hi there.

I've done it but... without a many-to-many of any kind. Here's the result:

Here is the model:

Many-to-Many turned into Classic

The S2 Additional Stage and S1 Additional Stage tables are hidden and they only store the stage of Non-Existent (see the report where you see Non-Existent instead of BLANK; yes, there are no (blank)'s in the report any more). I needed them to add the Non-Existent entry into the S1 Stages and S2 Stages tables in Power Query. Note that S2 Stages and S2 Snapshot Date have inactive relationships with the Snapshot table. I could have enabled them but I don't think it would be correct in any way in this model.

Here are the tables' visible fields:

The fields in Snapshot that link to the other tables have been hidden. You should not slice by them.

Of course, to create the dimensions I used Power Query to extract the right data from the columns of Snapshot and make it unique.

Here are the measures:

S1 Amount = 
var __isS2NonExistentStageVisible = 
    NOT ISEMPTY(
        FILTER(
            'S2 Stages',
            'S2 Stages'[S2 Stage] = "Non-Existent"
        )
    )
var __relevantIds =
    CALCULATETABLE(
        SUMMARIZE(
            Snapshot,
            Snapshot[Id]
        ),
        USERELATIONSHIP('S2 Snapshot Date'[Date], Snapshot[Date] ),
        USERELATIONSHIP('S2 Stages'[S2 Stage], Snapshot[Stage] ),
        ALL( 'S1 Snapshot Date' ),
        ALL( 'S1 Stages' )
    )
var __allIdsThatHaveS2Stage =
    CALCULATETABLE(
        SUMMARIZE(
            Snapshot,
            Snapshot[Id]
        ),
        USERELATIONSHIP('S2 Snapshot Date'[Date], Snapshot[Date] ),
        ALLEXCEPT( Snapshot, 'Unique IDs' )
    )
var __resultWithoutNonExistent =
    CALCULATE(
        SUM( Snapshot[Amount] ),
        INTERSECT(
            VALUES( Snapshot[Id] ),
            __relevantIds
        )
    )
var __resultWithNonExistent =
    CALCULATE(
        SUM( Snapshot[Amount] ),
        EXCEPT(
            VALUES( Snapshot[Id] ),
            __allIdsThatHaveS2Stage
        )
    )
var __result =
    __resultWithoutNonExistent +
        if( __isS2NonExistentStageVisible, __resultWithNonExistent )
return
    __result

S2 Amount = 
var __isS1NonExistentStageVisible = 
    NOT ISEMPTY(
        FILTER(
            'S1 Stages',
            'S1 Stages'[S1 Stage] = "Non-Existent"
        )
    )
var __relevantS1Ids =
    SUMMARIZE(
        Snapshot,
        Snapshot[Id]
    )
var __allIdsThatHaveS1Stage =
    CALCULATETABLE(
        SUMMARIZE(
            Snapshot,
            Snapshot[Id]
        ),
        ALL( 'S1 Stages' )
    )
var __visibleS2Ids =
    CALCULATETABLE(
        VALUES( Snapshot[Id] ),
        USERELATIONSHIP( Snapshot[Stage], 'S2 Stages'[S2 Stage] ),
        USERELATIONSHIP( Snapshot[Date], 'S2 Snapshot Date'[Date] ),
        ALLEXCEPT( Snapshot, 'Unique IDs' )
    )
var __resultWithoutNonExistent =
    CALCULATE(
        SUM( Snapshot[Amount] ),
        INTERSECT(
            __visibleS2Ids,
            __relevantS1Ids
        ),
        USERELATIONSHIP( Snapshot[Stage], 'S2 Stages'[S2 Stage] ),
        USERELATIONSHIP( Snapshot[Date], 'S2 Snapshot Date'[Date] ),
        ALL( 'S1 Snapshot Date' ),
        All( 'S1 Stages' )
    )
var __resultWithNonExistent =
    CALCULATE(
        SUM( Snapshot[Amount] ),
        EXCEPT(
            __visibleS2Ids,
            __allIdsThatHaveS1Stage
        ),
        USERELATIONSHIP( Snapshot[Stage], 'S2 Stages'[S2 Stage] ),
        USERELATIONSHIP( Snapshot[Date], 'S2 Snapshot Date'[Date] ),
        ALL( 'S1 Snapshot Date' ),
        All( 'S1 Stages' )
    )
var __result =
    __resultWithoutNonExistent +
        if( __isS1NonExistentStageVisible, __resultWithNonExistent )
return
    __result

I don't know how performant this code is. You have to check it yourself.

Best

Darek

[Anonymous]

Hi Darek,

I tried your solution and it somehow didn't work using my actual data.  And this is my bad.  I have over-simplified the data sample I provided.  Below is the link to my pbix file where my workaround is to use 3 separate visuals.  This has actual data source.  You seem very well-versed in DAX and I'm hoping you can help me find a way to show these in a single matrix table (instead of 3).

My current workaround using 3 separate visuals. My goal is to be able to show these in a single matrix table.

Link to pbix: https://drive.google.com/file/d/1KkMZsb-SK39InNKj9BlLgIYEWZLF-3in/view?usp=sharing

Best regards,

Ferdinand

[Anonymous]

Hi there.

Sorry for not having answered earlier but I was on hols out of the country. Only back today. I'll have a look at your file and see what the issue is.

Best

Darek

[Anonymous]

No worries Darek.  I really appreciate your help on this.

Best Regards,

Ferdie

[Anonymous]

Thank you so much Darek! Do you mind sharing the pbix file please?

Best regards,

Ferdinand