Measure to count distinct values as 1 over certain period of time range

[dbollini]

Hello,

I am trying to calculate the Distinct count of Colonoscopies for individual patients and i am having couple of issues below is the sample data:

1)Below is the measure i am using to get distinct count but now My measure has to calculate  and include the logic to “bundle” records that are within the same 72 hour range as 1 count and then distinict count of colon with start date or health plan id whichever user requests.

Colonoscopies = CALCULATE( DISTINCTCOUNT(Paid_View[Health Plan ID]), FILTER(Paid_View,Paid_View[DX_COLONO]="Colonoscopy"))

2)There is a drill through page which i created for this counts but in that drill through report it is showing all the patients in the list though the patient woth Dx_colono= colonscopy should only show but it shows office visits and all patients.

COUNTY	DX	DX_COLONO	Start Date	Health Plan ID	Member Name
Fort Bend	null	null	4/10/2015 12:00:00 AM	123456	Joe,D
Fort Bend	null	null	4/10/2015 12:00:00 AM	123456	John,D
Fort Bend	null	null	4/10/2015 12:00:00 AM	12356	John,C
Fort Bend	null	Colonoscopy	4/21/2015 12:00:00 AM	12456	David,C
Fort Bend	null	null	4/21/2015 12:00:00 AM	12345	David,F
Fort Bend	null	null	4/21/2015 12:00:00 AM	123456	Carol,C

Can someone help me with the correct measure and Drill through issue too thanks

[Anonymous]

Hi @dbollini ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[dbollini]

Hello,

Please find the .pbix file in the link below with some test data and please request so i can approve access thanks

https://www.dropbox.com/scl/fo/itl74m733kuqazj98tfjm/h?rlkey=fafzz5m81oojmwm89ngtiox3c&dl=0

[Anonymous]

HI @dbollini,

You can try to use the following measure formula to get the count of records that include in the records 72 hour ranges:

formula =
VAR currDate =
    MAX ( Paid_View[Start Date] )
VAR planlist =
    CALCULATE (
        VALUES ( Paid_View[Health Plan ID] ),
        FILTER ( Paid_View, Paid_View[DX_COLONO] = "Colonoscopy" )
    )
RETURN
    CALCULATE (
        COUNT ( Paid_View[Health Plan ID] ),
        FILTER (
            ALLSELECTED ( Paid_View ),
            [Health Plan ID]
                IN planlist
                    && [Start Date] >= currDate - 3
                    && [Start Date] <= currDate + 3
        )
    )

Regards,

Xiaoxin Sheng

[dbollini]

Thank you and i tried this formual but it throws an error like The function expects a table expression for arguement ",but a string numeric expression was used and highlights below and says incorrect

 IN planlist

[dbollini]

I am getting Error below when i am using the code and i trie dto modify with other stuff but same error

[Anonymous · Accepted solution]

HI @dbollini,

You can modify the variable to use CALCULATETABLE function, then it can be compared with in operator:

formula =
VAR currDate =
    MAX ( Paid_View[Start Date] )
VAR planlist =
    CALCULATETABLE (
        VALUES ( Paid_View[Health Plan ID] ),
        FILTER ( Paid_View, Paid_View[DX_COLONO] = "Colonoscopy" )
    )
RETURN
    CALCULATE (
        COUNT ( Paid_View[Health Plan ID] ),
        FILTER (
            ALLSELECTED ( Paid_View ),
            [Health Plan ID]
                IN planlist
                    && [Start Date] >= currDate - 3
                    && [Start Date] <= currDate + 3
        )
    )

Regards,

Xiaoxin Sheng

[dbollini]

Thank you for your help !