topic Re: Measure to count distinct values as 1 over certain period of time range in DAX Commands and Tips

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

dbollini — Fri, 23 Feb 2024 16:28:57 GMT

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

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

Anonymous — Fri, 23 Feb 2024 05:57:54 GMT

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

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

dbollini — Fri, 23 Feb 2024 16:22:40 GMT

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

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

Anonymous — Tue, 27 Feb 2024 08:17:09 GMT

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

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

dbollini — Tue, 27 Feb 2024 17:04:24 GMT

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

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

dbollini — Wed, 28 Feb 2024 17:01:12 GMT

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

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

Anonymous — Tue, 05 Mar 2024 07:45:45 GMT

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

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

dbollini — Tue, 05 Mar 2024 20:50:25 GMT

Thank you for your help !