Distinct Subtotals by Dynamic Time Intelligent Occurrence of Combinations

[rymerco]

Greetings to Everyone and Happy New Year 2018!

I have a unique problem that I have not been able to solve in DAX without a circumvented and inefficient method.  The problem I am having is that the dax measure needs to be a dynamic, time intelligent accrual of cumulative totals within date segments.

Here is an example where the first image is MASTERDATASET:

Date Range = Dynamic - but for this example, the time slicer is set for 1/1/17 to 1/11/17

I want the subtotals of Value1 within that date range.

The wrinkle is in how the subtotals are aggregated.

The result comes from:

Identifier1 AA has a combination with Identifier2 of

AA BB, AA DD, AA CC.  So I want to total all of Value1 in the instances where BB, CC, DD are Identifier1 in the date range.

Measure = Subtotal for AA = sum Value1 where BB, CC, DD is Identifer1 = 8 + 5 + 7 + 8 = 28

But I want this for all distinct values in Identifier1.  End result in the formula would subtotal all for AA, BB, CC, DD

AA = (BB AA) 8 + (CC BB) 5 + (CC AA) 8 + (DD AA) 7 = 28

description: because AA has 3 occurences in Identifier1 column with BB in Identifier2 [AA BB on 1/1/17], CC in Identifier2 [AA CC on 1/11/17], and DD in Identifier2 [AA DD on 1/5/17]. therefore, I want the total of Value1 in all occurences of BB, CC, DD as it occurs in Identifier1.

...furthermore, in PowerBI I would be able to also have it specify...

BB = (AA BB) 10 + (AA DD) 6 + (AA CC) 12 = 28

description: because BB has only 1 occurence in Identifier1 column with AA in Identifier2 [BB AA on 1/2/17].  Yet, AA has 3 occurences in Identifier1, as noted above.  Therefore, I want all of the totals where AA is in Identifier1.

CC = (BB AA) 8 + (AA BB) 10 + (AA DD) 6 + (AA CC) 12 = 36

description: CC has 2 occurences in Identifier1 with BB in Identifier2 [CC BB on 1/5/17] and AA in Identifier2 [CC AA on 1/2/17].

...and so on...

DD = (AA BB) 10 + (AA DD) 6 + (AA CC) 12 = 28

description: again, because there is only 1 occurence of DD in Identifier1 with the combo of AA in Identifier2 [DD AA on 1/8/17].

The measure would then produce the following visualization table dynamically in Power BI:

 

I would then want the numbers to dynamically change if I changed the date range to 1/1/17 to 1/2/17.

AA BB 1/1/17

BB AA 1/2/17

CC AA 1/2/17

The measure would produce the following visualization dynamically in Power BI in table format:

I hope this is not as confusing as I feel it reads.  This seems like it would be very simple to generate, but I cannot get it to work.

I would really appreciate the forum's assistance to help me crack the code here.

[v-ljerr-msft · Accepted solution]

Hi @rymerco,

Based on my test, the formula below show work in your scenario. 

Measure = 
VAR currentIdentifier1 =
    FIRSTNONBLANK ( Table1[Identifier1], 1 )
RETURN
    SUMX (
        FILTER (
            ALLSELECTED ( Table1 ),
            CONTAINS (
                FILTER ( ALLSELECTED ( Table1 ), Table1[Identifier1] = currentIdentifier1 ),
                Table1[Identifier2], Table1[Identifier1]
            )
        ),
        Table1[Value1]
    )

Here is the sample pbix file for your reference. 

Regards

[rymerco]

This appears to be the fix.  Thank you for your assistance.  Much appreciated as I was spinning in circles trying to find a more efficient solution.