Simple Dax to lookup mapping table and compare with datasets - Updated

[arikofather]

I have here a PBIX that helps my previous confusing post.  I need helo to figure out the issues.

I have a caculated column to extract a consoliaded status from the mappingt table.   The logic is not pickingup the exact status.  

\\

*Cal Column Consolidated Status =

    LOOKUPVALUE (

        'aaInvoice Status Mapping Dataset'[Consolidated Display],

        'aaInvoice Status Mapping Dataset'[App - Secondary Review Identifier],           related ('aaCoding App-Automation Prod DS from V'[Secondary Review] ),

        'aaInvoice Status Mapping Dataset'[App - Coding Status/Secondary Review Status], related ('aaCoding App-Automation Prod DS from V'[Status] ),

        'aaInvoice Status Mapping Dataset'[Oracle - coding Status],                      'aaOracle Dataset_V6.0'[Coding Status] ,

        'aaInvoice Status Mapping Dataset'[Oracle - Approval Status],                    'aaOracle Dataset_V6.0'[Approval Status],

        'aaInvoice Status Mapping Dataset'[Oracle - Payment Status],                     'aaOracle Dataset_V6.0'[Paid Status] ,

        'aaInvoice Status Mapping Dataset'[Oracle - On Hold],                            'aaOracle Dataset_V6.0'[On Hold],

        "Invalid Consolidated Status"

    )

Community AR.pbix

[rajendraongole1]

Hi @arikofather - i have created a measure to find the same. 

Please find the attached pbix file.

I am getting "Invalid Status" as output means the LOOKUPVALUE function is running correctly but not finding a matching row in your aaInvoice Status Mapping Dataset table.

Measure details:

Consolidated Status Measure =

VAR CurrentInvoiceID = SELECTEDVALUE('aaOracle Dataset_V6.0'[Invoice Id])

VAR CodingAppTable =

    CALCULATETABLE (

        'aaCoding App-Automation Prod DS from V',

        'aaCoding App-Automation Prod DS from V'[Invoice Id] = CurrentInvoiceID

    )

RETURN

    IF (

        NOT ISBLANK(CurrentInvoiceID),  -- Check if a single Invoice ID is selected

        CALCULATE (

            LOOKUPVALUE (

                'aaInvoice Status Mapping Dataset'[Consolidated Display],

                'aaInvoice Status Mapping Dataset'[App - Secondary Review Identifier], MAXX(CodingAppTable, [Secondary Review]),

                'aaInvoice Status Mapping Dataset'[App - Coding Status/Secondary Review Status], MAXX(CodingAppTable, [Status]),

                'aaInvoice Status Mapping Dataset'[Oracle - coding Status], SELECTEDVALUE('aaOracle Dataset_V6.0'[Coding Status]),

                'aaInvoice Status Mapping Dataset'[Oracle - Approval Status], SELECTEDVALUE('aaOracle Dataset_V6.0'[Approval Status]),

                'aaInvoice Status Mapping Dataset'[Oracle - Payment Status], SELECTEDVALUE('aaOracle Dataset_V6.0'[Paid Status]),

                'aaInvoice Status Mapping Dataset'[Oracle - On Hold], SELECTEDVALUE('aaOracle Dataset_V6.0'[On Hold]),

                "Invalid Status" -- This is an optional result if a match is not found

            )

        )

    )

Hope this works.

[arikofather]

@rajendraongole1 There are multiple matching rows matching row in the aaInvoice Status Mapping Dataset table.  

[arikofather · Accepted solution]

I was able to resolve this by TRIming all the columns of "aaInvoice Status Mapping Dataset".  It turned out that the records were not matching due to whitespace. 

[v-sshirivolu]

Hi @arikofather ,

I'm glad you found a solution and resloved  the query. Thank you very much for sharing here.

If you have further queries please reach out.

Thankyou for connecting with Microsoft Community Forum