Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Kaavya
Frequent Visitor

Iterate rows in a table based on multiple columns from another table

Hi, 

 

I have below table with the following info. I want to know if each of these rows is being retreated or treated for the first time. So basically a logical "yes" or "no". It has to be compared to another table which will have similar information. Both the tables will have multiple rows for each ID and there will be thousands of ID. Table 2 will have more records than table 1. Below is a sample info for one of the IDs.

 

Table 1:

Date.1ID.1Type.1
31/1/2416
24/1/2416
17/1/2418
10/1/2418
27/12/2311
20/12/23133

 

Table 2: (Sorted by ID.2 (asec) and Date.2 (desc)

Date.2ID.2Type.2Discharge
2/2/2416False
31/1/2416False
26/1/2416False
24/1/2418False
24/1/2416False
19/1/2418False
17/1/2418False
12/1/2418False
10/1/2418False
3/1/2411True
27/12/23133False
27/12/2311False
20/12/23133False

 

So, I want to know if each of the row in table is a yes or no by

  1. Check if it is a new record by filtering table 2 for all records with id.1 = id.2 and type.1 = type.2 and date.2 < date.1. If the filtered table does not return any records, then it is a yes.
  2. If there is any filtered records, check for below conditions.  
    1. if the record with latest date (Date.2) has discharge = "TRUE" then it is yes else
    2. If date.1 - date.2 > 7 then it is yes 
    3. else no

The process has to be repeated for each row in table 1. 

 

Expected Output:

 

Date.1ID.1Type.1Status
31/1/2416no
24/1/2416yes
17/1/2418no
10/1/2418yes
27/12/2311yes
20/12/23133yes

 

Many thanks, 

 

Kaavya 

1 ACCEPTED SOLUTION
v-xiandat-msft
Community Support
Community Support

Hi @Kaavya ,

amustafa's solution is right , but you need to transform Date.1 type to Date . When I used to use the datediff function with a text type, it would display a numeric error.Here are my results:

Below is my table1:

vxiandatmsft_0-1707803105849.png

Below is my table2:

vxiandatmsft_1-1707803125345.png

The following DAX might work for you:

Status = 
   var lat = FILTER('Table 2','Table 2'[ID.2] = 'Table 1'[ID.1] && 'Table 2'[Type.2] = 'Table 1'[Type.1] && 'Table 2'[Date.2] < 'Table 1'[Date.1])
   var A = CALCULATE(MAX('Table 2'[Date.2]),lat)
   var dis = SELECTEDVALUE('Table 2'[Discharge],A)
   RETURN
    IF(ISBLANK(A),"Yes",IF(dis = "True","yes",IF(DATEDIFF(A,'Table 1'[Date.1],DAY) > 7 , "yes","no")))

The final output is shown in the following figure:

vxiandatmsft_2-1707803184214.png

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

4 REPLIES 4
v-xiandat-msft
Community Support
Community Support

Hi @Kaavya ,

amustafa's solution is right , but you need to transform Date.1 type to Date . When I used to use the datediff function with a text type, it would display a numeric error.Here are my results:

Below is my table1:

vxiandatmsft_0-1707803105849.png

Below is my table2:

vxiandatmsft_1-1707803125345.png

The following DAX might work for you:

Status = 
   var lat = FILTER('Table 2','Table 2'[ID.2] = 'Table 1'[ID.1] && 'Table 2'[Type.2] = 'Table 1'[Type.1] && 'Table 2'[Date.2] < 'Table 1'[Date.1])
   var A = CALCULATE(MAX('Table 2'[Date.2]),lat)
   var dis = SELECTEDVALUE('Table 2'[Discharge],A)
   RETURN
    IF(ISBLANK(A),"Yes",IF(dis = "True","yes",IF(DATEDIFF(A,'Table 1'[Date.1],DAY) > 7 , "yes","no")))

The final output is shown in the following figure:

vxiandatmsft_2-1707803184214.png

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hi @v-xiandat-msft  Xianda Tag,

 

thank you so much for your reply.

 

i will give it a go.

 

Also is it possible to do this in power query? If so, can you please help with that.



thanks

 

kaavya

amustafa
Solution Sage
Solution Sage

Here's how you can create a Status DAX calculated column in your Table1.

 

Status = 
VAR latestRecord = CALCULATE(MAX(Table2[Date.2]), 
                             FILTER(Table2, 
                                    Table2[ID.2] = Table1[ID.1] && 
                                    Table2[Type.2] = Table1[Type.1] && 
                                    Table2[Date.2] < Table1[Date.1]))
VAR isDischarged = SELECTEDVALUE(Table2[Discharge], latestRecord)
RETURN
    IF(ISBLANK(latestRecord), "yes",
        IF(isDischarged = TRUE, "yes",
            IF(DATEDIFF(latestRecord, Table1[Date.1], DAY) > 7, "yes", "no")))

 

amustafa_0-1707715698657.png

 

Hi Amustafa,

 

thank you so much for your reply. It works great but the record against 17/1/23 should be a no as well. Because the date difference is less than 7 when compared to date 12/1/23 the second table. 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors