Referencing Parameter Table

[Anonymous]

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 with object along the top and size along the bottom, in an Excel spreadsheet). What is the best way to generate a cost column, in a way that makes it easy to change the cost parameter if necessary?

I appologize if this is posted twice - I'll delete the other post if so.

Thank you.

[Anonymous]

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]

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.

[Anonymous]

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 

[v-yulgu-msft]

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]

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 · Accepted solution]

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]

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.

[LivioLanzo]

Hi @Anonymous

You can do this by performing a merge operation with Power Query, here is how: https://support.office.com/en-us/article/merge-queries-power-query-fd157620-5470-4c0f-b132-7ca2616d17f9

If I understand correctly, you may first need to unpivot the table coming from the Excel spreadsheet