Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Referencing Parameter Table

Hi. I have a dataset with the two columns: object and size (queried from SQL Server). I would like to create a third column for cost. I know the cost based on the object and size (I have a cost table...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi Anonymous,

     

    You can first unpivot the reference table in Query Editor to change its structure to below:

     

    Then, in source data table, add a calculated column with below DAX.

    Cost =
LOOKUPVALUE (
    'Reference Table'[Value],
    'Reference Table'[Fruit/Size], 'Source Table'[Fruit Sold],
    'Reference Table'[Type], 'Source Table'[Size]
)

     

    If you don't want to unpivot table, you can use below DAX to generate a cost column.

    Cost =
IF (
    'Source Table'[Size] = "Large",
    LOOKUPVALUE (
        'Reference Table'[Large],
        'Reference Table'[Fruit/Size], 'Source Table'[Fruit Sold]
    ),
    IF (
        'Source Table'[Size] = "Medium",
        LOOKUPVALUE (
            'Reference Table'[Medium],
            'Reference Table'[Fruit/Size], 'Source Table'[Fruit Sold]
        ),
        LOOKUPVALUE (
            'Reference Table'[Small],
            'Reference Table'[Fruit/Size], 'Source Table'[Fruit Sold]
        )
    )
)

    Best regards,

    Yuliana Gu