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" )
GeraldGEmerick's avatar
GeraldGEmerick
Super User
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" )