Measure Lookupvalue in-between two table

Measure Lookupvalue in-between two tableJump to Best AnswerFollow
Ab Ab
Ab AbOct 27, 2022 12:31 PM
Hi, I have 3 tables are Data, Report and Priority. In Data table contains duplicate items with …
1. Measure Lookupvalue in-between two table

Ab Ab
Posted Oct 27, 2022 12:31 PM
Hi,

I have 3 tables are Data, Report and Priority.

In Data table contains duplicate items with unique code and qty, report table contain unique item only and Priority table contain order for the code.

In data table the same item has two different codes are “CHE” and “RCB” so I would like to pull the code and qty against code “CHE” only that’s my first priority and there is no “CHE” then pick from “RCB” that’s second priority.

In data table the items has duplicate with code so I created two different columns in data table (count, Unique count) to get the unique count for code against the items.

CODE ORDER =
VAR CODE_ORDER = DATA[CODE]
RETURN
LOOKUPVALUE(PRIORITY[ORDER],PRIORITY[CODE],CODE_ORDER)
CODE ORDER UNIQUE COUNT =
VAR CODE_ORDER = DATA[CODE ORDER]
RETURN
IF(CODE_ORDER = CALCULATE(MIN(DATA[CODE ORDER]),ALLEXCEPT(DATA,DATA[ITEM])),1,0)
and finally I used lookupvalue function to bring the code and qty against the item in report table by using below mentioned formula

CODE = LOOKUPVALUE(DATA[CODE],DATA[ITEM],REPORT[ITEM],DATA[CODE ORDER UNIQUE COUNT],1)
QTY = LOOKUPVALUE(DATA[QTY],DATA[ITEM],REPORT[ITEM],DATA[CODE ORDER UNIQUE COUNT],1)

Question:

1. I want to get the same result by using DAX New calculated column option without duplication in data table (Count and unique count column in data table- Creating DAX from data table into report table without priority table).
2. I want to get the same result by using Measure option without duplication in data table (Count and unique count column in data table- Creating Measure from data table into report table without priority table).

Data Model.

DM1.PNG

Data Table

DT3.PNG

Report Table

RT2.PNG

Priority Table:

PT4.JPG

LOOKUPVALUR-MES&CAL-27-10-22.pbix

Dropbox remove preview

LOOKUPVALUR-MES&CAL-27-10-22.pbix
Dropbox is a free service that lets you bring your photos, docs, and videos anywhere and share them easily. Never email yourself a file again!
View this on Dropbox >

——————————
Ab Ab
Engineering
——————————

2. RE: Measure Lookupvalue in-between two table Best Answer

Kaz Shakir
Posted Oct 29, 2022 07:02 PM | view attached
@Ab Ab,
Here’s how I would approach this problem. First, in the DATA table when you have two lines for the same ITEM, you need to have another column (a calculated column) that would determine which of those two rows I want to use.​ For example in your DATA table you have two rows for ITEM = 123 (see screenshot below), but you only want to use the first row and not the second.

So, I would add another column that will check the Priorty table for these CODEs and tell me which one has the lowest priority, and I want to mark that row as the one I want to use. To do this, I create a calculated column with the following formula:
UseThisRow =
VAR thisItem = DATA[ITEM]
VAR filteredData = FILTER(DATA, DATA[ITEM] = thisItem)
VAR minOrder = MINX(filteredData, RELATED(PRIORITY[ORDER]))
RETURN
RELATED(PRIORITY[ORDER]) = minOrder​
This column will just have a True or False depending on whether we should include that row in our report or not.

Now, I can create a visual just based on the DATA table that shows the results I want to see (this is a Matrix visual):

Alternatively, if you want to add calculated columns to the REPORT table that show the CODE and QTY, I would create them using the following formulas:
CODE2 =
VAR thisItem = REPORT[ITEM]
VAR filteredData = FILTER(DATA, DATA[UseThisRow] && DATA[ITEM] = thisItem)
RETURN
MINX(filteredData, DATA[CODE])​
QTY2 =
VAR thisItem = REPORT[ITEM]
VAR filteredData = FILTER(DATA, DATA[UseThisRow] && DATA[ITEM] = thisItem)
RETURN
MINX(filteredData, DATA[QTY])
The same matrix visual built from the REPORT table would look like this:

Hope that helps. Please let us know if that is the result you are seeking.

Kaz.

——————————
Kaz Shakir
Sr. Program Manager, Asset Planning
TN
——————————

Attachment(s)

pbixLOOKUPVALUR-MES&CAL-27-10-22_r2.pbix