locked
LOOKUPVALUE with many to many RRS feed

  • Question

  • I have two tables with many to many, connected as many to one over Calendar Table.

    Table [Date], [Name]

    Date Name
    01-01-16 John123
    01-01-16 John234
    01-01-16 John345
    04-01-16 John123
    04-01-16 John567
    06-01-16 John678

    ...

    Table_Size has [Date], [Name], [Size] 

    Date Name Size
    01-01-16 John123 XL
    04-01-16 John123 M
    10-01-16 John123 L
    01-01-16 John234 M
    11-01-16 John234 L

    ...

    Table Size records changes of Size for the Name, For example between 01-01-2016 Jonh is XL size, and after 04-01-2016 is M size, and later after 10-01-16 he is L :

    I would like to combine those changes into calculated column in Table for all dates. I would like to see those Size values in Table for every date for example 01-01-2016 till 03-01-2016 should be XL. Please note that Size must be keep as "Text" value.  

    Sample excel file on onedrive


    • Edited by lamaa Sunday, October 15, 2017 1:33 PM
    Sunday, October 15, 2017 1:31 PM

Answers

  • I have found DAX solution for my problem. 

    First I have changed lookup table to schema:

    [Start Date], [End Date], [Name], [Value]

    Thanks to this I am able to "vlookup" with filters by using combination of:

    =CALCULATE(FIRSTNONBLANK(lookuptable,1),

    FILTER(lookuptable,  lookuptable[Start Date]<=table[Date]

    && lookuptable[End Date] <= table[Date]

    && lookuptable[Name] = table[Name]))

    • Marked as answer by lamaa Tuesday, October 17, 2017 12:31 AM
    Tuesday, October 17, 2017 12:30 AM

All replies

  • Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Fill 2017 Calendar Table with daily Name and Size, using PQ.
    http://www.mediafire.com/file/hnazfklah63sj74/10_15_17.xlsx
    http://www.mediafire.com/file/2is1b1nr4eh8n8m/10_15_17.pdf

    Sunday, October 15, 2017 11:35 PM
  • Thank You for your suggestion. You have created even better example file.

    I was thinking about similar solution with PQ but due to size of original table it is not ideal solution. I would prefer to use DAX only.

    Maybe someone comes with other ideas. 

    Sunday, October 15, 2017 11:50 PM
  • Hi Lamaa,

    Thanks for your question.

    According to your description, you may need to create a calculated table which will do a CROSSJOIN between distinct Table[Name] and distinct Calendar[Date], after that you can add a calculated column called size to that calculated table using LOOKUPVALUE function and then fill the blank value in another calculated column.  

    Since you are using Power Pivot, it does not support calculated table. Thus, It is impossible to achieve your requirement with DAX only in Power Pivot.  In this scenario, you may consider to use Power BI instead.

    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Monday, October 16, 2017 7:44 AM
  • Hi Willson,

    Thanks your for suggestion.

    Your solution sounds similar to Herbert Seidenberg. 

    Both above solutions are based on filling lookup tables with all dates. I am not 100% convinced this is" elegant" solution but so far it is the only one that might work. I have to stick with Excel at this point so I will try PQ road proposed by Herbert Seidenberg.

    I was hoping there might be some DAX timeinteligence magic that I am missing. Since no one has proposed any magic DAX solution without expanding lookup table maybe this case shows that there is a place for new DAX function(s) or changes to current ones ? 

     

    Monday, October 16, 2017 10:46 AM
  • I have found DAX solution for my problem. 

    First I have changed lookup table to schema:

    [Start Date], [End Date], [Name], [Value]

    Thanks to this I am able to "vlookup" with filters by using combination of:

    =CALCULATE(FIRSTNONBLANK(lookuptable,1),

    FILTER(lookuptable,  lookuptable[Start Date]<=table[Date]

    && lookuptable[End Date] <= table[Date]

    && lookuptable[Name] = table[Name]))

    • Marked as answer by lamaa Tuesday, October 17, 2017 12:31 AM
    Tuesday, October 17, 2017 12:30 AM