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 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        
PrjIDPrjNameGateG0 DateG2 DateG4 DateG5 DateGate Reports MissingDocument Link MissingAll Reports available
P1Prj1G502-Feb-202402-Jun-202402-Oct-202402-Dec-2024G4  No
P2Prj2G402-Feb-2024 02-Dec-2024   Yes
P3Prj3G502-Feb-202402-Dec-202402-Dec-202402-Feb-2025G0, G2, G4, G5 No
P4Prj4G502-Feb-2024  02-Feb-2025 G0No
P5Prj5G202-Feb-202402-Jun-202402-Apr-202602-Dec-2026G0 No
ReportsTable   
PrjIDGateLast Gate DateDoc Link
P1G002-Feb-2024link
P1G202-Jun-2024link
P1G502-Dec-2024link
P2G002-Feb-2024link
P2G402-Dec-2024 
P4G002-Feb-2005 
P5G202-Jun-2024link
P4G502-Feb-2005link
Master Table     
PrjIDPrjNameGateG0 DateG2 DateG4 DateG5 Date
P1Prj1G502-Feb-202402-Jun-202402-Oct-202402-Dec-2024
P2Prj2G402-Feb-2024 02-Dec-2024 
P3Prj3G502-Feb-202402-Dec-202402-Dec-202402-Feb-2025
P4Prj4G502-Feb-2024  02-Feb-2025
P5Prj5G302-Feb-202402-Jun-202402-Apr-202602-Dec-2026
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" )

5 Replies

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

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

      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'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.