Forum Discussion

Yonatan1984's avatar
Yonatan1984
Helper 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
v-janeyg-msft's avatar
v-janeyg-msft
Community Support
4 years ago

Hi, Yonatan1984  

 

If the data type and the table structure are the same, it is logically impossible to have problems. Did you download my file and compare it with your source file? According to the sample you provided, we can only help you here unless you can provide further details.

 

Best Regards,
Community Support Team _ Janey
Yonatan1984's avatar
Yonatan1984
Helper 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
    Community 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
    Community 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