Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 🙂
Solved! Go to 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).
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).
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?
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 🙂
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |