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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

You could also try using the "What if" Parameter in the Powere BI Desktop This will allow your Power BI Reports to be change able for the end user 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi sailkitty. I took a look at what if parameters, and I don't think that's what I'm looking for. I do not need user input. I need a custom column that pulls a cost from a lookup table based on the fruit and size column. I would like the lookup table to be editable in query editor. Please let me know if I misunderstood. Thanks though.