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

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
7 years ago

Hi Anonymous,

 

I have a cost table with object along the top and size along the bottom

How is this table like? Please show us an example.

 

 What is the best way to generate a cost column, in a way that makes it easy to change the cost parameter if necessary

Do you want a cost column which is dynamically calculated based on slicer selection? What value should be slicer items?

 

Please provide sample data and expected result to make your requirement more clear. How to Get Your Question Answered Quickly

 

Best regards,

Yuliana Gu

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi. Below is a simple example for the data table, where the first two columns are queried from a MSSQL database. I would like the cost column to be calculated, based on a lookup table that gives cost for a particular fruit and size. 

     

     

     

    Below is an example of the lookup table I would use:

     

     

     

    And here is my expected result (the cost column is calculated based on the first two columns using the lookup table):

     

     

     

    Right now I am using many conditional statements to accomplish this. It would be nice if I could simple reference a lookup (second picture) table in Power Query M.

     

    Thank you.

     

    • v-yulgu-msft's avatar
      v-yulgu-msft
      Microsoft Employee
      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

      I tried to create a data relationship between the two tables and then use the RELATED function in DAX but I cannot create a relationship as my cost parameter table has two fruits with the same costs:

       

       

       

      Is this the right approach, or do I need to do a merge between the tables to get the desired output? I am essentially looking for something like VLOOKUP in Excel. 

       

      Thanks.