Forum Discussion
Help required to write DAX measure
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" )
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 ?
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?
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.
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