Forum Discussion

manojk_pbi's avatar
manojk_pbi
Helper V
6 months ago
Solved

Help required to write DAX measure

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 be...
Need Help
Show and Tell
Tips and Tricks
  • GeraldGEmerick's avatar
    GeraldGEmerick
    6 months ago

    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" )
manojk_pbi's avatar
manojk_pbi
Helper V
6 months ago

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's avatar
GeraldGEmerick
Super User
6 months ago

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