Data Modelling Doubt

[Madhans]

I want the doc to be added in the table visual based on the take over and handover date in the main table. 

Could someone please assist me with this and provide guidance on the current modeling I have performed? I would appreciate any advice on how to improve it.


Regards,
S. Madhan

[Madhans]

Hi v-xuxinyi-msf,

Thanks for the effort!!!

Point 1:
kindly find my corrected Calendar table below
It contains all date from 2023 to 2024.

Point 2:

It looks like you've created a separate table named "Query 1" in Power BI, but my actual requirement isn't to have a physical table. Instead, I need the data to be displayed only within the table visual. I also wonder whether it's necessary to have another bridge table for DOC, similar to the one for IMO. I tried creating it, but it didn't work as expected to filter document names based on takeover and handover dates.

I've been trying to resolve this for quite some time but still haven't achieved the desired outcome. I would really appreciate your help.

Regards,
S. Madhan

[Anonymous]

Hi @Madhans 

First of all, I have a question to confirm with you. Does the Date in the sample result you gave come from the Calendar table? But in the data you gave, the date in the sample result does not exist in the Calendar table, but exists in the Inspection table.

After my test, the DOC in the Main table is passed to the Inspection table through IMO, but it still seems that it cannot determine the IMO corresponding to DOC. Could you please consider implementing this effect in Transform Data (Power Query)? If you can, the following is my test process for your reference. During the test, I put the date in the Inspection into the result table. If I understand it wrong, please feel free to correct me.

Transform data -> New Source -> Blank Query -> Advanced Editor

Then put the following into Advanced Editor.

let
    Source = Table.NestedJoin(Inspection, {"IMO"}, Main, {"IMO"}, "Main", JoinKind.FullOuter),
    #"Expanded Main" = Table.ExpandTableColumn(Source, "Main", {"Doc", "Take Date", "Hand Date"}, {"Doc", "Take Date", "Hand Date"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Main", "Custom", each if [Inspection Date] >= [Take Date] and [Inspection Date] <= [Hand Date] then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Take Date", "Hand Date", "Custom"})
in
    #"Removed Columns"

Close & Apply

Output:

Best Regards,
Yulia Xu

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