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 / Unit is determined based on CPT codes.

I have written 1 measure that counts the visits / Units that use these codes:

 
Qualifying Units = CALCULATE(SUM(MERGE_BillingSUMMARY[Units]),filter('MERGE_BillingSUMMARY',(MERGE_BillingSUMMARY[CPT Code] IN { "90791", "90832","90834","90837","90839","90846","90847","90853", "90839"}))

I need help with 3 task

First, I need a measure that sets the Max Units Allowed

     Adults (18+) can have 12 visits / units

     Children (0-17) can have 24 visits / units

Second, I need a measure for Remaining Units

      This should basically be Max Units - Qualifying Units

Third and final, I need to set a flag for the adults at 9 units and at 21 for children. I'd like to do that in Conditional Formatting if possible

 

Thanks for any insight and as a side note, my date range is set by a slicer

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.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    According to your statement, I think I could understand your requirement. However, your data model will determind how to create the measure. Could you share a sample file without sensitive data with me? And you can show me a screenshot with the result you want to make me easier to find the solution.

     

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Works like a charm! Thanks for that last piece!!!!