Add shift to fact table

[Stabben23]

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.

[Stabben23]

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.

[Ashish_Mathur · Accepted solution]

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]

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]))))

[mwegener]

Hi @Stabben23 ,

look at this.

https://community.powerbi.com/t5/Desktop/Join-with-greater-than-AND-less-than-to-join-date-time-tabl...

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.