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

[manojk_pbi]

Thanks @GeraldGEmerick for your quick reply and the solution. The solution is working as expected.

Gates report missing is showing all the gates, instead it should pick based on the availibility of dates in the particluar Gate dates. How can we add this clause to DAX ?

[GeraldGEmerick]

@manojk_pbi I'm not quite certain that I fully understand the requirement regarding the gates and date intervals. Can you provide additional information on this item?

[manojk_pbi]

@GeraldGEmerick , thanks for your response. I will try to explain with more details here.

The requirement is, All projects will have 2 mandatory gates G0 & G5. In the master table also these gates should have dates defined and correspondingly we should see a entry for each gate in reports table with document link. If entries missing in reports table then it is missing report case.

If dates not defined for some gates in master table, we don't need to highlight those gate reports as missing.

eg: in given data P4 has not defined dates for G2 & G4 so we don't need to show it in report missing columns. With current logic it is getting displayed.

In P2, G2 Date is not defined in master, so we will not report this as missing. 

Hope my explanation helps.

[GeraldGEmerick]

@manojk_pbi Here is a revision to the measure. I have to say that the measure is made much more complicated by the fact that you have your dates in 4 different columns versus being unpivoted into rows. That said, I realize that would then introduce a many-to-many relationship unless you broke the master table into two separate tables (probably a better overall model design). 

The trouble is compounded by the fact that DAX does not like dynamic table variables (variables that could return different tables depending on an IF statement for example). Therefore, the solution gets rather messy.

Gate Reports Missing = 
VAR _AllGates = { "G0", "G2", "G4", "G5" }
VAR _Gates15 = { "G0", "G5" }
VAR _Gates125 = { "G0", "G2", "G5" }
VAR _Gates145 = { "G0", "G4", "G5" }
VAR _G2Date = MAX( 'MasterTable'[G2 Date] )
VAR _G4Date = MAX( 'MasterTable'[G4 Date] )
VAR _ProjectGates = DISTINCT( 'ReportsTable'[Gate] )
VAR _MaxGate = SELECTEDVALUE( MasterTable[Gate] )
VAR _MissingAllGates = EXCEPT( FILTER( _AllGates, [Value] <= _MaxGate ), _ProjectGates )
VAR _MissingGates15 = EXCEPT( FILTER( _Gates15, [Value] <= _MaxGate ), _ProjectGates )
VAR _MissingGates125 = EXCEPT( FILTER( _Gates125, [Value] <= _MaxGate ), _ProjectGates )
VAR _MissingGates145 = EXCEPT( FILTER( _Gates145, [Value] <= _MaxGate ), _ProjectGates )
VAR _Return = 
    SWITCH( TRUE(),
        _G2Date = BLANK() && _G4Date = BLANK(), CONCATENATEX( _MissingGates15, [Value], ", " ),
        _G2Date = BLANK(), CONCATENATEX( _MissingGates145, [Value], ", " ),
        _G4Date = BLANK(), CONCATENATEX( _MissingGates125, [Value], ", " ),
        CONCATENATEX( _MissingAllGates, [Value], ", " )
    )
RETURN _Return

[GeraldGEmerick · Accepted solution]

@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" )