Forum Discussion

Frequent Visitor

2 years ago

Creating a Episode reference with DAX based on start and end dates across multiple rows

Hi All I am trying to use DAX to create a unique episode reference for each row that is part of a single episode. As a starting point I have already created unique Index in Power Query for eac...

"DAX help"

"DAX"

CJLewis87

Frequent Visitor

2 years ago

https://drive.google.com/file/d/1qoQKlGZv2qzZxfmP3H89Di47mDcc2Ob4/view?usp=sharing

Hi All

I have attached a sample pbix file on Google Drive. The file contains the query with any confidential data removed. The data includes "ID", "Placement Grouping", "Finance Start Date", "Finance End Date".

With that in mind, I want to create a unique reference number for each episode of care, which I am defining as having the same ID number, same placement grouping and consecutive dates across rows, without any break in dates. So for example, ID number 4636 has 5 seperate rows making up the single episode, so I'd like perhaps "Res4636-1" to show on all rows forming that episode with a column called "Episode ID".