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