Measure in Live Connection - Incorrect Total

[sabilahmed]

Hello,

I know there have been many solutions on incorrect totals from measures in Live Connection, but I can't seem to figure out the solution for my measure:

No of Sessions = 
 VAR _encounters =
    CALCULATE (
        [Amount],
        FILTER (
            ALL ( 'Profitability Account' ),
            'Profitability Account'[Description] = "Encounters"
        )
    )
 VAR _NoOfDays = Calculate(DISTINCTCOUNT('Time'[CalendarDate]),Filter ('Time', 'Time'[DayOfWeekdayDescription] <> "Sunday" && 'Time'[DayOfWeekdayDescription] <> "Saturday"  ))
 VAR _NoOfSession = _NoOfDays * SWITCH (
        TRUE (),
        _encounters <= 2, 0,
        _encounters >= 2
            && _encounters <= 10, 1,
        _encounters > 10, 2
    )
 RETURN _NoOfSession

This gives me the following result (I have covered up the practice locations):

You can see the No of Sessions total is incorrect.

I even attempted something different by trying something different. I wanted to try something like DISTINCTCOUNTX (but this does not exist in DAX) - so I found the below solution:

No of Sessions New = 
 VAR _NoOfDays = COUNTROWS( DISTINCT( SELECTCOLUMNS( 'Time', "CalendarDate",'Time'[DayOfWeekdayDescription] <> "Sunday" && 'Time'[DayOfWeekdayDescription] <> "Saturday"  )))
 VAR _NoOfSession = _NoOfDays * IF (
        [Encounters] <= 2, 0,
        IF([Encounters] >= 2
            && [Encounters] <= 10, 1,
        2
    ))
 RETURN _NoOfSession

I replaced the VAR _encounters with a measure instead [encounters] . And I replaced the SWITCH statement for an IF statement instead to see if that will do anything different, but it still doesn't work:

The following is the Encounters measure (it's exactly the same as in the original No of Sessions DAX:

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

Please help me write a new DAX measure that will fix this issue and give me correct Totals 🙂

[sabilahmed · Accepted solution]

Solved! I used ADDCOLUMNS and SUMMARIZE:

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 separate 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"  )
                )

Results:

(Doctor Days is simply a measure: No of Sessions divided by 2).

[tamerj1]

Hi @sabilahmed 

Location, Practice and Rendering Name from which table(s)? If multiple tables are involved in this visual, would you please advise the relationships?

[sabilahmed]

Hi @tamerj1 

The Location Practice and Rendering Name doesn'y matter because when I put the measure in a card alone I still get the wrong total. See below:

So you can see the Total issue has nothing to do with the Locations column.

These are the key relationships. Remember, this is a Live Connection Analysis Services:

Let me know if you need anything else. And thank you for your support 🙂