Forum Discussion

rymerco's avatar
rymerco
Frequent Visitor
8 years ago
Solved

Distinct Subtotals by Dynamic Time Intelligent Occurrence of Combinations

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 th...
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi rymerco,

     

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

    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. :smileyhappy:

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi rymerco,

 

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

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. :smileyhappy:

 

Regards

  • rymerco's avatar
    rymerco
    Frequent Visitor
    8 years ago

    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.