Lookup value with multiple scenario in Power Bi

Question

I have a two tables are Data and Report.

Data Table:

In Data table contain two columns are item and supplier code. The item column contain text & number and supplier code column contain as a text.

Summary if CHE record in DATA:

if any of the items have CHE records in the data table, get the values if only one CHE record, and if there is more than one CHE record then compare each column (length and Supplier) and if any of the columns has different value then return MIXED otherwise return Actual value

Summary if no CHE record

if only one none CHE record in the data table then returns the actual values but if more than one none CHE records in the data table for that time, compare the values of each column, and if any column value is different then return MIXED otherwise value.

Summary: no record found in DATA table:

Return N/A Logic

My first priority is “CHE” and second priority is “KKR” in order get the length and supplier from Data table into the Report Table.

Scenario 1;

If two different supplier {“CHE” & “KKR”} for the same item in data table then I would like to get the data (length and supplier) against “CHE” in report table according to the item. (Please refer the item 123 and 5678 in both tables ”Data” and “Report”)

Scenario 2;

If supplier not repeated according to the item and the item contain either any of these supplier {“CHE” or “KKR”} in data table, in these scenario pull the data (length and supplier) against the item in report table based on the availability in data table. (Please refer the item 5555, 6666, 9876 and 2345 in both tables ”Data” and “Report”)

Scenario 3;

If supplier not repeated according to the item and the item contain either any of these supplier {“CHE” or “KKR”} but the length & supplier are different in data table in these scenario return as a text “Mixed” were belongs to (column; either length or supplier or both) in report table according to the item. (Please refer the item 123456 and 567 in both tables ”Data” and “Report”)

Scenario 4;

If two different supplier {“CHE” & “KKR”} for the same item in data table but the length and supplier are variable in data table in this scenario return as a text “Mixed” were belongs to (column; either length or supplier or both) in report table according to the item. (Please refer the item 116 in both tables ”Data” and “Report”)

Scenario 5;

If records not found in date table the return as “NA” in report table according to the item. (Please refer the items in report in table 4444, 12 and 10)

I am looking for calculated column option in order to achieve my result.

Current logic

I created New table for order for supplier priority

I created two helper column for running count and unique count

Finally I am applying the following calculated column in report table

LENGTH = LOOKUPVALUE(
    DATA[Length],
    DATA[ITEM],REPORT[ITEM],
    DATA[UNIQUE_COUNT],1,"NA")

it almost working fine but it will give a wrong result (item 123456 and 567) were my desired is "Mixed" in report table. I highlighted in red and green snapshot for your reference.

My final result look like this

Herewith attached the PowerBi file for your reference https://www.dropbox.com/s/p81uy12tfh1htwu/AUOM1.pbix?dl=0

Can you please advise.

It's always useful to describe the actual business problem you're trying to solve instead of your attempted solution, which may be flawed in itself (and most likely is but the looks of it). Please, describe in more detail the business logic - what do you want to present to the report user and what source data you have. — W.B.
thank you so much for your reply and advise. I update more details and logic in original post. Can you please advise when you have a time and please let me know if you need a more details. — johon

Alexis Olson Alexis Olson · Accepted Answer · 2021-02-09T14:45:24

It's not entirely clear what you're trying to do but I think this measure pattern may help you:

IF (
    DISTINCTCOUNT ( DATA[SUPPLIER] ) > 1,
    "MIXED",
    SELECTEDVALUE ( DATA[SUPPLIER], "NA" )
)

For more than one value, you get MIXED. For one value, you get that single value. For no values, you get NA.

Lookup value with multiple scenario in Power Bi

1 Answers