Measure in Live Connection - Incorrect Total
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 🙂
