Forum Discussion

sabilahmed's avatar
sabilahmed
Icon for Resolver I rankResolver I
3 years ago
Solved

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 ğŸ™‚

cube
Live Connection
Need Assistance
Need Dax
Need Help
Need Help DAX
Need Help DAX measure
Need Help in Power Bi
Tabular Model
  • sabilahmed's avatar
    sabilahmed
    3 years ago

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

     

3 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
      sabilahmed
      Icon for Resolver I rankResolver I
      3 years ago

      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 ğŸ™‚

  • sabilahmed's avatar
    sabilahmed
    Icon for Resolver I rankResolver I
    3 years ago

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