Lookup multiple criteria

[Saxon10]

Hi,

I have a two tables are Data and Report.

Data Table:

In Data table I have the following columns are Item, Qty, Length, Height, Width and comments.

The Item column contain duplicated and it’s stored as number and Text format in Excel.

The Qty, Length, Width and Height contain number format and some it’s stored as a text in Excel.

Comments column contain two different text which is CHE and KKR in Excel.

Report Table:

In Report table I have Item column only.

Desired Result:

My first priority is “CHE” and second priority is “KKR” in order get the qty, dimension and comments from Data table into the Report Table.

Scenario 1;

The same item contain two different comments which is “CHE” and “KKR” and two different data set (qty and dimension data – Length, width ,Height and comments) in date table then I would like to pull the data from “CHE” according to the item in my report table. (Please refer the item 123 and 5678 in both tables ”Data” and “Report”)

Scenario 2;

Sometime the item not repeated and any one these comments “CHE” or “KKR” according to the item in data table, in these scenario bring the data (qty and dimension data – Length, width, Height and comments) against the item in report table. (Please refer the item 5555, 6666, 9876 and 2345 in both tables ”Data” and “Report”)

Scenario 3;

Sometime the same item contain any one of these comments “CHE” or “KKR” but the qty & dimension are different in data table in these scenario return as a text “Mixed” in report table according to the item. (Please refer the item 123456 and 567 in both tables ”Data” and “Report”)

Scenario 4;

The same item and contain any of one these comments “CHE” or “KKR” but the qty and dimension are variable any of these columns are Qty, Length, Width and Height in data table in this scenario return as a text “Mixed” were belongs to in report table according to the item. (Please refer the item 453 and 112 in both tables ”Data” and “Report”)

Scenario 5;

The same item and two different comments “CHE” and “KKR” but the qty and dimension are variable any of these columns are Qty, Length, Width and Height in data table in this scenario return as a text “Mixed” were belongs to in report table according to the item. (Please refer the item 116 in both tables ”Data” and “Report”)

Scenario 6;

If item not available 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.

Herewith attached the Power BI and Excel file for your reference.

https://www.dropbox.com/s/afojz3a3ss01cud/AUOM.pbix?dl=0

https://www.dropbox.com/scl/fi/a4gdbso8tgtjja4yzht22/MPCK.xlsx?dl=0&rlkey=vh0sw7gzroo91rtyn6t0d3jas

Can you please advise.

[parry2k · Accepted solution]

@Saxon10 hey solution attached, two things:

- in your desired output 901 and 902 has a wrong calculation for height, it supposes to be MIXED instead of the value because there is a variation in the data.

- not sure what logic you are using for comments to show "MIXED" vs actual comments.

here is the DAX code for one of the columns and the file is attached, you can tweak it as per your need:

Qty = 
VAR __f = IF ( CALCULATE ( COUNTROWS ( DATA ), KEEPFILTERS ( DATA[COMMENTS] = "CHE" ) ) >= 1,  "CHE", "KKR" ) //check if CHE record exists
VAR __t =  FILTER ( RELATEDTABLE ( DATA ), DATA[COMMENTS] = __f )  //get data table, filter on comments
VAR __r = COUNTROWS ( __t )     //count number of rows
VAR __s = SUMX ( __t, [Qty] )   //sum qty of all the rows for the column we are interested in, in this case it is QTY
VAR __a = DIVIDE ( __s, __r )   //divide sum with number of rows
VAR __m = MAXX ( __t, [Qty] )   //get one value from the related table
VAR __c = __m == __a            //check if avg is equal to the one value, if equal, it means all the rows have the same value for that column
VAR __result =                  //final output, if no value found then N/A, if rows has same value then return value else return MIXED
IF ( ISBLANK ( __m ), "N/A", 
    IF ( __c, FORMAT ( __m, "General Number" ), "MIXED" )
)
RETURN __result

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@Saxon10 that's great news. It took a while to understand the problem. Well, if it ends well, it is all well.

Cheers!!

[parry2k · Accepted solution]

@Saxon10 here is another version, I streamlined some of the calculations. See attached, it has all the columns with number 1 and also there are old columns.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Saxon10]

Thank you so much for your help and time. Your both solution working amazing. Thank you.

[parry2k · Accepted solution]

@Saxon10 hey solution attached, two things:

- in your desired output 901 and 902 has a wrong calculation for height, it supposes to be MIXED instead of the value because there is a variation in the data.

- not sure what logic you are using for comments to show "MIXED" vs actual comments.

here is the DAX code for one of the columns and the file is attached, you can tweak it as per your need:

Qty = 
VAR __f = IF ( CALCULATE ( COUNTROWS ( DATA ), KEEPFILTERS ( DATA[COMMENTS] = "CHE" ) ) >= 1,  "CHE", "KKR" ) //check if CHE record exists
VAR __t =  FILTER ( RELATEDTABLE ( DATA ), DATA[COMMENTS] = __f )  //get data table, filter on comments
VAR __r = COUNTROWS ( __t )     //count number of rows
VAR __s = SUMX ( __t, [Qty] )   //sum qty of all the rows for the column we are interested in, in this case it is QTY
VAR __a = DIVIDE ( __s, __r )   //divide sum with number of rows
VAR __m = MAXX ( __t, [Qty] )   //get one value from the related table
VAR __c = __m == __a            //check if avg is equal to the one value, if equal, it means all the rows have the same value for that column
VAR __result =                  //final output, if no value found then N/A, if rows has same value then return value else return MIXED
IF ( ISBLANK ( __m ), "N/A", 
    IF ( __c, FORMAT ( __m, "General Number" ), "MIXED" )
)
RETURN __result

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@Saxon10 so if I understood correctly:

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 (dimension and qty) 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

Is this correct understanding?

[parry2k]

@Saxon10 scenarios are bit confusing:

= what is the difference between 3 and 4?

= scenario 5, I guess in this case it should only return CHE rows, no? otherwise it contradicts with scenario 1 which should only return CHE rows, or I misread it.

You explained nicely but how you put it in plain English, like if record found in data table then get all the records, something like that..

[Saxon10]

Data and Report table;

DATA TABLE
ITEM QTY LENGTH WIDTH HEIGHT COMMENTS
123 10 200 300 400 CHE
123 15 210 310 410 KKR
5678 20 220 320 420 CHE
5678 25 230 330 430 KKR
5555 30 240 340 440 CHE
6666 35 250 350 450 KKR
9876 40 260 360 460 CHE
2345 45 270 370 470 KKR
901 50 280 380 480 CHE
901 50 280 380 490 CHE
902 60 300 400 500 KKR
902 60 300 400 510 KKR

REPORT TABLE
ITEM QTY LENGTH WIDTH HEIGHT COMMENTS
123 10 200 300 400 CHE
5678 20 220 320 420 CHE
5555 30 240 340 440 CHE
6666 35 250 350 450 KKR
9876 40 260 360 460 CHE
2345 45 270 370 470 KKR