Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Measure Lookupvalue in-between two table

    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

    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)