DAX - Help Needed (row level check)

[bangerasid]

Hi Team,

I have the below scenario.

I am trying to create the master table based on End of Week & Attendance tables.

However, when I am doing this, i want to consider the Date of Joining as well.

I am unable to figure out a dax for the same.
Basically, i want to check
if(date of joining < end of week then "Present/Absent" else "blank"),

I will then remove all the records having blank values as the result.

@tamerj1 @truptis @Vijay_A_Verma @SpartaBI @amitchandak @Ashish_Mathur @Greg_Deckler 

[Ashish_Mathur]

You should have a proper date column i.e. without the ordinals such as rd, th.  Share data in a format that i can paste into MS Excel with my suggested change.

[bangerasid]

End of Week

11/06/2022

18/06/2022

25/06/2022

ID	EOW	Hire Date
10	18/06/2022	13/06/2022
10	25/06/2022	13/06/2022

[Ashish_Mathur · Accepted solution]

Hi,

You may download the solution workbook from here.  I have used the Query Editor in MS Excel to solve the question.  Implement the same steps in PowerBI Desktop.

[bangerasid]

Hi Ashish,

Thanks for the response. I want to do it at DAX level and not at power query, reason being the dataset is huge, for each ID there are around 10k rows.

I am just trying to compare if(joined on > end of week, "Exclude", "Include").
However, to do this, I am not able to get the end of week value in the highlighted area.

values are coming for all the fields except for when the student has no attendance.

Used the below formula.
m_endofweek measure = VALUES(Weeks[End of Week])

[Ashish_Mathur]

Hi,

Write this measure

=if(max(Data[Joined On])>max(Data[End of week]),"Exclude","Include")

Hope this helps.

[bangerasid]

I am getting the following output, however, the expected output should have "Exclude" in place of "Include" at the highlighted cell.

[amitchandak]

@bangerasid , merge using left join EOW and end of week in power query

and then add a custom column in the power query

if [Date of joining] < [End of week] then "Present"

Merge : https://radacad.com/append-vs-merge-in-power-bi-and-power-query

[bangerasid]

Thanks for replying.

Merging wont fetch the missing week for each student right? 
Let me know if there is a way to get the missing week for every student using join.