Creating Unique Group numbers based on conditions on DAX.

[countryfan_nt]

Hello friends, Hope all is well! I need your help please.

I wish like the image below to create a unique Group numbering based on a number of conditions on DAX or PQ. The image below is a sample but in reality it is a CSV file that was imported by Power Query.

The conditions are:

A. If all have same project name (column G)
B. If all have same PatientID (column E)
C. If Clinic in one Row is “ER Clinic” and in other rows any other clinic (Column C)
D. AND the date of ER Clinic “InvoiceDate” must be equal to OR lower than any other clinic Date by only 2 days (column D)

Thank you very much in advance! 

[Anonymous · Accepted solution]

HI @countryfan_nt,

I'd like to suggest you enter to query editor to use 'add index column' and 'group by' features to achieve your requirements. You can refer to the following blog to modify the grouped fields to assign index numbers based on the groups:

Create Row Number for Each Group in Power BI using Power Query - RADACAD

For Dax version, you can refer to the below thread to use ISONORAFTER function to generate a sort number based on multiple columns:

Sorting by multiple columns 
Regards,

Xiaoxin Sheng