Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

General Count / Sum function not working

I need help Creating 2 measures that rely on varibles. The task = create a tabular report that shows the number of qualifying visits a patient has had during a given date range. Qualifying Visit / U...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    I think you can create some measures by IF function.

    AC / FLAG = 
IF ( MAX ( 'MERGE_BillingSUMMARY'[VISIT AGE] ) >= 18, "Adult", "Child" )
    Qualifying Units = 
CALCULATE (
    SUM ( MERGE_BillingSUMMARY[Units] ),
    FILTER (
        'MERGE_BillingSUMMARY',
        MERGE_BillingSUMMARY[CPT Code]
            IN {
            "90791",
            "90832",
            "90834",
            "90837",
            "90839",
            "90846",
            "90847",
            "90853",
            "90839"
        }
    )
)
    REMAINING UNITS = 
VAR _MAX =
    IF ( MAX ( MERGE_BillingSUMMARY[VISIT AGE] ) >= 18, 12, 24 )
RETURN
    _MAX - [Qualifying Units]

    Then build a table visual.

    If you want to build a date range slicer, I suggest you to create a DimDate table and then build a relationship between two tables.

    DimDate = 
CALENDARAUTO()

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Rico, 

A screenshot of sample data is below. The highlighted codes represent the only service dates and units that can be counted. As a reminder, people 0-17 can have 24 visits and 18+ can have 12 before we have to get reauteorization from the insurance company.

 

The end goal is to produce a tabular report like this that shows the total allowed units (service dates) based on the adult or child varible - (minua) the qualifying units to equal the Remaining Units column.

 

 

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous ,

 

I think you can create some measures by IF function.

AC / FLAG = 
IF ( MAX ( 'MERGE_BillingSUMMARY'[VISIT AGE] ) >= 18, "Adult", "Child" )
Qualifying Units = 
CALCULATE (
    SUM ( MERGE_BillingSUMMARY[Units] ),
    FILTER (
        'MERGE_BillingSUMMARY',
        MERGE_BillingSUMMARY[CPT Code]
            IN {
            "90791",
            "90832",
            "90834",
            "90837",
            "90839",
            "90846",
            "90847",
            "90853",
            "90839"
        }
    )
)
REMAINING UNITS = 
VAR _MAX =
    IF ( MAX ( MERGE_BillingSUMMARY[VISIT AGE] ) >= 18, 12, 24 )
RETURN
    _MAX - [Qualifying Units]

Then build a table visual.

If you want to build a date range slicer, I suggest you to create a DimDate table and then build a relationship between two tables.

DimDate = 
CALENDARAUTO()

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.