Latest Vendor name

DAX
1

I have 3 tables pulled from SAP they are OITM, PCH1, and OPCH. The PCH1 and OPCH tables are related by DocNum and OITM and PCH1 are related by ItemCode. The OPCH table contains the vendor names that I need in my visualization table. The visualization table uses the Item code from OITM to list out all the items. My issue is OPCH has multiple rows for each item code. I need to get the latest one referring to the create date in that table and extract the vendor name.

2

I tried Data Mentor to solve this and this was the feedback:

image
image2492Ă—984 148 KB

However, when I implemented the measure worked fine but I had issues creating the table.

image
image1374Ă—118 19 KB

Any suggestions?

3

You’re more likely to get a valuable response if you post a .pbix file. Just a minimal working example so a user can start with that, develop and test a response and then provide it back to you in a meaningful way.

1 Like
4

Here is the PBIx file:

It was too big to upload.

5

@michael.wescott7226,

Try this revised version of LatestVendorName:

LatestVendorName = 

// The original version of this measure was intended to retrieve the latest vendor name
// for each item code. However, it incorrectly calculated the latest date across the entire
// 'OPCH - A/P Invoice' table, not per 'ItemCode'. This led to inaccurate results when
// trying to find the latest vendor for each specific item code.

// Original version:
// VAR LatestDate =
//     MAXX('OPCH - A/P Invoice', 'OPCH - A/P Invoice'[CreateDate])
// RETURN
//     CALCULATE(
//         VALUES('OPCH - A/P Invoice'[CardName]),
//         FILTER('OPCH - A/P Invoice', 'OPCH - A/P Invoice'[CreateDate] = LatestDate)
//     )

// The revised measure correctly addresses the requirement by first determining the latest
// 'DocEntry' for each 'ItemCode' in 'PCH1 - A/P Invoice Rows' and then using this to find
// the corresponding latest vendor name in 'OPCH - A/P Invoice'. This approach ensures
// that the latest vendor is calculated for each specific 'ItemCode', adhering to the
// relationships between the tables.

// Capture the current 'ItemCode' from the 'OITM: Item Information' table
VAR CurrentItemCode = SELECTEDVALUE('OITM: Item Information'[ItemCode])

// Calculate the latest 'DocEntry' for the current 'ItemCode' in 'PCH1 - A/P Invoice Rows'
VAR LatestDocEntry =
    CALCULATE(
        MAXX(
            FILTER(
                'PCH1 - A/P Invoice Rows', 
                'PCH1 - A/P Invoice Rows'[ItemCode] = CurrentItemCode
            ), 
            'PCH1 - A/P Invoice Rows'[DocEntry]
        )
    )

// Determine the latest vendor name ('CardName') from 'OPCH - A/P Invoice' based on the 
// latest 'DocEntry' identified for the current 'ItemCode'
VAR LatestVendorName =
    CALCULATE(
        MAXX(
            FILTER(
                'OPCH - A/P Invoice', 
                'OPCH - A/P Invoice'[DocEntry] = LatestDocEntry
            ), 
            'OPCH - A/P Invoice'[CardName]
        )
    )

// Return the latest vendor name for the current 'ItemCode'
RETURN
    LatestVendorName

This measure may require some tinkering around on your part, but the idea should be clear.

By the way, that was h-u-g-e .pbix file to step through. In the future, you may want to take a few more steps and create a minimal working example (MWE) to ensure quicker results with less effort from the community. These include a) isolating the relevant data by creating a subset that includes only the tables and fields directly related to the issue. This not only makes the file smaller and easier to manage but also helps focus on the specific problem; b) anonymizing sensitive data while retaining the structure and type of the original data; c) simplifying relationships and replicating the issue with as few tables as possible; d) including only necessary calculations, measures, and visuals; e) documenting the data model by for example including a screenshot of the the relationship view; and f) including sample output or desired results.

hth

6

Thanks again. That worked up to a point. It got the latest vendor into the vendor table in the data model. However, when I tried to add that column to the visual on my report it just gave me an alphabetical list of all the vendors that repeats for every item code. I thought that was because I needed a relationship to the OITM table but I get a circular reference when I try to add that.

7

I take it there’s a reason for creating the physical table Vendor Table, so I won’t question that. It isn’t necessary because you can add the measure directly into a visual.

LatestVendorName calculates the latest vendor name based on the current context of ItemCode so it’s essential to ensure it’s used in a visual where ItemCode is present. If the visual doesn’t have ItemCode as a part of its context, the measure won’t work as intended.

If I place LatestVendorName in a table visual with ItemCode I see a list of all the item codes along with the latest vendor for each:

image image

What visual are you placing it in and what is the filter context? Do you have ItemCode in that visual?

8

I am placing it in the main table at the top of page 1. Turns out I thought it was using item code from OITM but it was from OITW. I switched it and it seems to be working now. The report is very slow when updating though.

9

Oh good.

The slowness you’re experiencing might be related to your data model. You could take a few steps and improve performance, such as by eliminating: unnecessary columns and tables, many-to-many relationships, and bidrectional crossfilters.

Have you read Russo and Ferrari’s The Definitive Guide to DAX?

10

I have not read it. I will check it out and look into cleaning it up. Thanks.

11

Here’s another potential strategy for you.

If you model is slow then you likely need to solve this in the model. Maybe making the tables a lot smaller.

12

Sometimes (actually most times) DAX isn’t the only answer. See if you can work through more strategies with Data Mentor

13