Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sabilahmed
Resolver I
Resolver I

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

sabilahmed_0-1663231927445.png

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:

 

sabilahmed_1-1663233303102.png

 

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 🙂

1 ACCEPTED SOLUTION
sabilahmed
Resolver I
Resolver I

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:

 

sabilahmed_0-1663517712414.png

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

 

View solution in original post

3 REPLIES 3
sabilahmed
Resolver I
Resolver I

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:

 

sabilahmed_0-1663517712414.png

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

 

tamerj1
Super User
Super User

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:

 

sabilahmed_0-1663238960825.png

 

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:

 

sabilahmed_1-1663239160899.png

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors