topic Re: Creating a Episode reference with DAX based on start and end dates across multiple rows in DAX Commands and Tips

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

CJLewis87 — Thu, 23 May 2024 09:35:07 GMT

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 each person as defined by their ID number before loading into my report.

An episode should be a collection of all rows for the same Index number whereby the Finance Start Date is either the same as the previous rows Finance End Date or +1 days. So for Index 0, I want all 3 of those rows to be assigned the same ID number as the dates are consectutive from 04/01/18 to 21/02/18. If there was then another entry with a break in dates, I'd want a new reference creating and so on and so forth.

Many thanks in advance!

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

lbendlin — Thu, 23 May 2024 21:47:38 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

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

CJLewis87 — Tue, 28 May 2024 15:25:57 GMT

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".

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

lbendlin — Thu, 30 May 2024 00:12:37 GMT

Your sample data is inconclusive. All IDs have contiguous date intervals meaning each has a single episode.

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.