ADDCOLUMNS and SUMMARIZE - incorrect Total (Live Connection)

[sabilahmed]

Please see below issue:

As you can see, for the No of Sessions, the total should be 9, but i'm getting a total of 32 insead which is super weird.

Since we're using a Live Connection, I don't have access to calculated columns. See below DAX I used:

No of Sessions = 

VAR SessionsTable =

        ADDCOLUMNS (    
            SUMMARIZE (
            'Location-Practice-Rendering',
            'Location-Practice-Rendering'[LeafName]
            ),
            "Total Encounters",
                [Encounters],
            "No of Days Total",
                [No of Days],
            "No of Sessions Per Day",
                IF ( [Encounters] <= 2, 0,
                IF ( [Encounters] > 10, 2, 1
                    )
                )
        )

        RETURN SUMX( SessionsTable, [No of Days Total] * [No of Sessions Per Day])

Where [Encounters] and [No of Days] are measures:

Encounters = CALCULATE (
                [Amount],
                FILTER (
                    ALL ( 'Profitability Account' ),
                    'Profitability Account'[Description] = "Encounters"
                ))

No of Days = 
    CALCULATE (
                DISTINCTCOUNT (
                'Time'[CalendarDate]),
                FILTER (
                'Time',
                'Time'[DayOfWeekdayDescription] <> "Sunday"
                && 'Time'[DayOfWeekdayDescription] <> "Saturday"  )
                )

We created the following DAX measure which gives us the correct results, BUT it takes hours to load, where as my DAX above takes seconds.

The following DAX gives us correct results:

No Of Sessions Day = 
VAR _encounters =
 CALCULATE (
 DISTINCTCOUNT ( 'Transaction Attributes'[Encounter Number__EBM__Encounter Number] ),
 FILTER (
 'Transaction Attributes',
 'Base Measures'[Encounters] > 0
 )
 )
RETURN
 SWITCH (
 TRUE (),
 _encounters <= 2, 0,
 _encounters >= 2
 && _encounters <= 10, 1,
 _encounters > 10, 2
 )

 


Total No of Sessions = 
VAR SessionsTable =
 SUMMARIZE (
 'Fact',
 'Fact'[CustomerID],
 'Fact'[CalendarDate],
 "No of Days Total",
 CALCULATE (
 DISTINCTCOUNT ( 'Fact'[CalendarDate] )
 ),
 "No of Sessions Per Day", [No Of Sessions Day]
 )
RETURN
 SUMX (
 SessionsTable,
 [No of Days Total] * [No of Sessions Per Day]
 )

Here are the results from the above DAX:

Can you please help me amened my original DAX so I can get the correct results? The ADDCOLUMNS SUMMARIZE works a lot faster. Unless we can fix the second DAX to make it faster?

@amitchandak - you have any thoughts on this issue?

Any help would be much appreciated 🙂

[johnt75]

I think your first calculation isn't giving the correct results because you're not including the date in the summary. You could try and amend the 2nd calculation to

Total No of Sessions =
VAR SessionsTable =
    ADDCOLUMNS (
        SUMMARIZE ( 'Fact', 'Fact'[CustomerID], 'Fact'[CalendarDate] ),
        "No of Days Total", CALCULATE ( COUNTROWS ( VALUES ( 'Fact'[CalendarDate] ) ) ),
        "No of Sessions Per Day", [No Of Sessions Day]
    )
RETURN
    SUMX ( SessionsTable, [No of Days Total] * [No of Sessions Per Day] )

I think that should be quicker

[sabilahmed]

Hi @johnt75 

Thanks for getting back to me. Unfortunately that didn't work - still encountering performance issue. I ran some tests to see where the performance issue is coming from and it is coming from the the meansure:

No Of Sessions Day = 
VAR _encounters =
    CALCULATE (
        DISTINCTCOUNT ( 'Transaction Attributes'[Encounter Number__EBM__Encounter Number] ),
        FILTER (
            'Transaction Attributes',
            'Base Measures'[Encounters] > 0
        )
    )
RETURN
    SWITCH (
        TRUE (),
        _encounters <= 2, 0,
        _encounters >= 2
            && _encounters <= 10, 1,
        _encounters > 10, 2
    )

Our transaction data is huge (over 12 million rows). So i'm guessing the calculation is running iterations through all line items? But this shouldn't be any issue right? I have worked with large datasets before but this is the first time i'm encountering this problem.

Addtional information you'd like to know we are running off of Tabular Cube (Analysis Services) Live Connection. Could this also be an issue?

PLease kindly advise.

[johnt75]

A couple of thoughts. Is the [Encounter Number__EBM__Encounter Number] column unique or can there be multiple rows in the 'Transaction Attributes' table with the same value? If it is unique then you can replace the DISTINCTCOUNT with COUNTROWS, which will be much faster.

What is the [Encounters] measure ? The FILTER is having to iterate over all the 12m transactions and execute this measure, so that needs to be optimised too. It might even be possible to do away with that filter and instead apply filters to the columns used in the measure directly inside CALCULATE, without needing FILTER.