Forum Discussion

manojk_pbi's avatar
manojk_pbi
Helper V
6 months ago
Solved

Need help in writing DAX for calculated Column/Measure

Hello Friends,

 

I have two tables like below, I need to compare the data in master table with transaction table to check all entries available, if anything missing report it.

 

Master Data

PrjIDPrjNameLast Passed Gate
P1Prj1G5
P2Prj2G4
P3Prj3G5
P4Prj4G5

 

Output table

PrjIDPrjNameLast Passed GateMissing Gate
P1Prj1G5G4
P2Prj2G4G2
P3Prj3G5All 
P4Prj4G5 

 

TransactionTable

PrjIDGateGate Date
P1G002-Feb-2024
P1G202-Jun-2024
P1G502-Dec-2024
P2G002-Feb-2024
P2G402-Dec-2024
P4G002-Feb-2005
P4G202-Jun-2024
P4G402-Feb-2005
P4G502-Feb-2006
Need Help
Show and Tell
Tips and Tricks
  • mdaatifraza5556's avatar
    mdaatifraza5556
    6 months ago

    Hi manojk_pbi 

     

     

    Frist Create one table using below dax 

    Gate Order =
    DATATABLE(
    "Gate", STRING,
    "GateSeq", INTEGER,
    {
    {"G0", 0},
    {"G2", 2},
    {"G4", 4},
    {"G5", 5}
    }
    )



    Can you please try the below DAX measures ?

    Missing Gate =
    VAR CurrentPrj = SELECTEDVALUE('Master Data'[PrjID])
    VAR LastGate =
        LOOKUPVALUE(
            'Gate Order'[GateSeq],
            'Gate Order'[Gate],
            SELECTEDVALUE('Master Data'[Last Passed Gate])
        )

    VAR ExpectedGates =
        FILTER(
            ALL('Gate Order'),
            'Gate Order'[GateSeq] < LastGate
        )

    VAR ActualGates =
        CALCULATETABLE(
            VALUES(TransactionTable[Gate]),
            TransactionTable[PrjID] = CurrentPrj
        )

    VAR Missing =
        EXCEPT(
            SELECTCOLUMNS(ExpectedGates, "Gate", 'Gate Order'[Gate]),
            ActualGates
        )

    RETURN
    IF(
        COUNTROWS(Missing) = 0,
        "All",
        CONCATENATEX(Missing, [Gate], ", ")
    )

    Result

     



    If this answers you questions, kindly accept it as a solution and give kudos.

2 Replies

  • mdaatifraza5556's avatar
    mdaatifraza5556
    Super User
    6 months ago

    Hi manojk_pbi 

     

     

    Frist Create one table using below dax 

    Gate Order =
    DATATABLE(
    "Gate", STRING,
    "GateSeq", INTEGER,
    {
    {"G0", 0},
    {"G2", 2},
    {"G4", 4},
    {"G5", 5}
    }
    )



    Can you please try the below DAX measures ?

    Missing Gate =
    VAR CurrentPrj = SELECTEDVALUE('Master Data'[PrjID])
    VAR LastGate =
        LOOKUPVALUE(
            'Gate Order'[GateSeq],
            'Gate Order'[Gate],
            SELECTEDVALUE('Master Data'[Last Passed Gate])
        )

    VAR ExpectedGates =
        FILTER(
            ALL('Gate Order'),
            'Gate Order'[GateSeq] < LastGate
        )

    VAR ActualGates =
        CALCULATETABLE(
            VALUES(TransactionTable[Gate]),
            TransactionTable[PrjID] = CurrentPrj
        )

    VAR Missing =
        EXCEPT(
            SELECTCOLUMNS(ExpectedGates, "Gate", 'Gate Order'[Gate]),
            ActualGates
        )

    RETURN
    IF(
        COUNTROWS(Missing) = 0,
        "All",
        CONCATENATEX(Missing, [Gate], ", ")
    )

    Result

     



    If this answers you questions, kindly accept it as a solution and give kudos.
    • manojk_pbi's avatar
      manojk_pbi
      Helper V
      6 months ago

      Thanks for your quick reply. Let me try the sample provided by you.