Forum Discussion

Stabben23's avatar
Stabben23
Helper I
6 years ago
Solved

Add shift to fact table

Hi guys,

 

I want to add shift to my fact table, I have a dimension with start and end timestamp and a fact with timestamp.

Now I want to map the shift field into facttable. 

All solutions is OK, connections between tables or dax formulas. What I want to achieve is that I can use shift with visualizations.

My Timestamp in fact is connected to a calendartable. Expected is my mapped field.

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    In Table1, write this calculated column formula

    =LOOKUPVALUE(Table2[Shift],Table2[DateStart],CALCULATE(MIN(Table2[Datastart]),FILTER(Table2,Table2[DateStart]<=EARLIER(Table1[TimeStamp])&&Table2[DateEnd]>=EARLIER(Table1[TimeStamp]))))

    Hope this helps.

4 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    In Table1, write this calculated column formula

    =LOOKUPVALUE(Table2[Shift],Table2[DateStart],CALCULATE(MIN(Table2[Datastart]),FILTER(Table2,Table2[DateStart]<=EARLIER(Table1[TimeStamp])&&Table2[DateEnd]>=EARLIER(Table1[TimeStamp]))))

    Hope this helps.

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi Stabben23 

     

    Try new measure to the FactTable

    Expected = CALCULATE(max(dimTable[Shift]);filter(all(dimTable);and(dimTable[DateStart]<=SELECTEDVALUE(FactTable[TimeStamp]);dimTable[DateEnd]>SELECTEDVALUE(FactTable[TimeStamp]))))
     

     

  • Stabben23's avatar
    Stabben23
    Helper I
    6 years ago

    Thanks to all,

    Great to see how other solve this common issue in Power BI.

    I like the lookupvalue solution best and this was pretty near what I have play around with.