Forum Discussion

Yonatan1984's avatar
Yonatan1984
Icon for Helper I rankHelper I
4 years ago
Solved

Search Part Cost

Hello to everyone! I'm trying to see the part cost rate of my Factory. I've an table from the DB that there is all the costs of all parts of the factory. There are item numbers that appear several...
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    Yonatan1984 

     

    If the field types of the two tables are synchronized, it does not matter whether text or number.

    Try this:

    Column = 
VAR a =
    MAXX (
        FILTER (
            PART_COST_RATE,
            [PART_CODE] = EARLIER ( 'Table'[PART_CODE] )
                && [EFECT_FROM_DATE] <= EARLIER ( 'Table'[MANUFACTURE DATE] )
        ),
        [EFECT_FROM_DATE]
    )
RETURN
    MAXX (
        FILTER (
            PART_COST_RATE,
            [EFECT_FROM_DATE] = a
                && [PART_CODE] = EARLIER ( 'Table'[PART_CODE] )
        ),
        [RATE]
    )

    Are you sure there are matching results in the two tables?

     

    Best Regards,
    Community Support Team _ Janey
Yonatan1984's avatar
Yonatan1984
Icon for Helper I rankHelper I
4 years ago

Hi v-janeyg-msft 

I checked, and the format of part_code in the tables is text and the date format is date in short date.

I checked your model, and it seems that the part_code format in your model is whole number.

There's a screenshot:

 

 

v-janeyg-msft's avatar
v-janeyg-msft
Icon for Community Support rankCommunity Support
4 years ago

Yonatan1984 

 

If the field types of the two tables are synchronized, it does not matter whether text or number.

Try this:

Column = 
VAR a =
    MAXX (
        FILTER (
            PART_COST_RATE,
            [PART_CODE] = EARLIER ( 'Table'[PART_CODE] )
                && [EFECT_FROM_DATE] <= EARLIER ( 'Table'[MANUFACTURE DATE] )
        ),
        [EFECT_FROM_DATE]
    )
RETURN
    MAXX (
        FILTER (
            PART_COST_RATE,
            [EFECT_FROM_DATE] = a
                && [PART_CODE] = EARLIER ( 'Table'[PART_CODE] )
        ),
        [RATE]
    )

Are you sure there are matching results in the two tables?

 

Best Regards,
Community Support Team _ Janey
  • v-janeyg-msft's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Yonatan1984 

     

    There is my code in the sample I gave you at the beginning... It's okay now.

     

    Best Regards,
    Community Support Team _ Janey