I've conected power bi to our SQL server. And I'm trying to gather the historic changes with its timestamps. this are stored in called snapshots tables. Problem that are different snapshot tables and the Key one "commandsnapshots" has no relations within the SQL server. My guess is that all the gathering work is hardcoded to avoid redundant relationships between tables.
That means that I should code that in power BI. I tried to do it by merging tables but there are so many tables to megre that the resulta hasn't been really productive.
My approach to it was to creat a DAX column that gathers de dates with If and lookupvalue sentences. This will mean a huge contatenated If sentences becase there are 49 different commands.
I was wondering If there's a more efficient way to do multiple lookupvalues with multiple condictions. I've been searching around the forum but didn't find a solution for me.
I'll show you just one of the examples. See image below:
What I'm looking is to get a column named "Date" in CommadSnashot table that contains the Batches[CreationDate] if CommandTypeKey[CommandType] = 5... and so on with multiple commandTypes gathering dates from different tables. If that makes sense.
@PepeB your requirement is not fully clear, and the image is so small to understand the diagram. Can you put some sample data in excel sheet and expected output and share that file to understand the problem.
Did I answer your question? Mark my post as a solution.
Proud to be a Datanaut! Appreciate your Kudos🙂 Feel free to email me with any of your BI needs.