topic Re: Measure in Live Connection - Incorrect Total in DAX Commands and Tips

Measure in Live Connection - Incorrect Total

sabilahmed — Thu, 15 Sep 2022 09:26:09 GMT

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 🙂

Re: Measure in Live Connection - Incorrect Total

tamerj1 — Thu, 15 Sep 2022 10:10:44 GMT

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?

Re: Measure in Live Connection - Incorrect Total

sabilahmed — Thu, 15 Sep 2022 10:53:28 GMT

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 🙂

Re: Measure in Live Connection - Incorrect Total

sabilahmed — Sun, 18 Sep 2022 16:18:10 GMT

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).