Help required to write DAX measure

[manojk_pbi]

Hello Friends,

I have one scenario to acheive, pls suggest me what is the best approach and how this can be handled. I have provided sample input data and expected output in table. 

I have below table with project details (master table) and reporttable is kind of transaction table to store the report details.

in Master table for every project gate dates are defined. If gate is already passed then we should have a corresponding entry in reports table with gate report links.

Use case : We need to loop trough the master table and identify what all project entries missing in reporttable or document links for each gate defined. Every project has planned dates for gates (all gates are not mandatory). We should have G0 & G5 mandatory gates for each projects. In few cases we have report for G2 & G4 as well. If date is not defined in Master table then we don't need to track report in reportstable.

Output table has expected data after applying the logic. 

Please someone suggest the best approach here and how this can be handled. Is it better to create calculated column or create a calculated measre and use it in visualization.

Output Table
PrjID	PrjName	Gate	G0 Date	G2 Date	G4 Date	G5 Date	Gate Reports Missing	Document Link Missing	All Reports available
P1	Prj1	G5	02-Feb-2024	02-Jun-2024	02-Oct-2024	02-Dec-2024	G4		No
P2	Prj2	G4	02-Feb-2024		02-Dec-2024				Yes
P3	Prj3	G5	02-Feb-2024	02-Dec-2024	02-Dec-2024	02-Feb-2025	G0, G2, G4, G5		No
P4	Prj4	G5	02-Feb-2024			02-Feb-2025		G0	No
P5	Prj5	G2	02-Feb-2024	02-Jun-2024	02-Apr-2026	02-Dec-2026	G0		No

ReportsTable
PrjID	Gate	Last Gate Date	Doc Link
P1	G0	02-Feb-2024	link
P1	G2	02-Jun-2024	link
P1	G5	02-Dec-2024	link
P2	G0	02-Feb-2024	link
P2	G4	02-Dec-2024
P4	G0	02-Feb-2005
P5	G2	02-Jun-2024	link
P4	G5	02-Feb-2005	link

Master Table
PrjID	PrjName	Gate	G0 Date	G2 Date	G4 Date	G5 Date
P1	Prj1	G5	02-Feb-2024	02-Jun-2024	02-Oct-2024	02-Dec-2024
P2	Prj2	G4	02-Feb-2024		02-Dec-2024
P3	Prj3	G5	02-Feb-2024	02-Dec-2024	02-Dec-2024	02-Feb-2025
P4	Prj4	G5	02-Feb-2024			02-Feb-2025
P5	Prj5	G3	02-Feb-2024	02-Jun-2024	02-Apr-2026	02-Dec-2026

[GeraldGEmerick]

@manojk_pbi Try seeing if the attached PBIX file has the DAX calculations that you are looking for. 

Gate Reports Missing = 
VAR _AllGates = { "G0", "G1", "G2", "G3", "G4", "G5" }
VAR _ProjectGates = DISTINCT( 'ReportsTable'[Gate] )
VAR _MaxGate = SELECTEDVALUE( MasterTable[Gate] )
VAR _MissingGates = EXCEPT( FILTER( _AllGates, [Value] <= _MaxGate ), _ProjectGates )
VAR _Return = CONCATENATEX( _MissingGates, [Value], ", " )
RETURN _Return


Document Link Missing = 
VAR _Table = FILTER( 'ReportsTable', [Doc Link] = BLANK() )
VAR _Return = CONCATENATEX( SELECTCOLUMNS( _Table, "Gate", [Gate] ), [Gate], ", " )
RETURN _Return



All Reports Available = IF( ISBLANK( [Gate Reports Missing] ) && ISBLANK( [Document Link Missing] ), "Yes", "No" )