if-statement: Lookup value with multiple scenario in Power Bi

lundi 8 février 2021

Lookup value with multiple scenario in Power Bi

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.

Case 1 - 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

Case 2: 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.

Case 3: no record found in DATA table:

Return N/A

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