Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
If you're a DAX expert! I need your help, please 🙂
So, I have a table with START & End Date, using this fields i need to build another table with a continuous date between the precedent fields.
As i said, this is my input :
And this what i'm looking to build :
Thanks for your help.
Solved! Go to Solution.
Any particular reason why do you want to do this with DAX? You can also use powerquery to achieve the same result
Hi,
In the Query Editor window, create this custom column
={Number.From([Start])..Number.From([End])}
Hope this helps.
Any particular reason why do you want to do this with DAX? You can also use powerquery to achieve the same result
Hooo, @ChandeepChhabra it looks like cool :
Can you please send me the Book1.xls, to understand the steps.
🙂
Check this out:
AgentTable = VAR tmpCalendar = CALENDAR(MIN(Agents[Start]),MAX(Agents[End])) VAR tmpAgent1 = FILTER(Agents,[ID]=1) VAR tmpAgent2 = FILTER(Agents,[ID]=2) VAR tmpTable1 = SELECTCOLUMNS( FILTER( GENERATE(tmpAgent1,tmpCalendar), [Date]>=[Start] && [Date]<=[End] ), "ID",[ID],"Agent",[Agent],"Date",[Date] ) VAR tmpTable2 = SELECTCOLUMNS( FILTER( GENERATE(tmpAgent2,tmpCalendar), [Date]>=[Start] && [Date]<=[End] ), "ID",[ID],"Agent",[Agent],"Date",[Date] ) RETURN UNION(tmpTable1,tmpTable2)
Thanks greg, but i don't know how much agent i'll have 😞
Well, then you have a problem because of your varying start and end dates. 😞
I might have time to take a look at this later or perhaps @Phil_Seamark or someone could assist with this as well.
Might also have to rework your data a little to unpivot your start and end date columns, that might be a path towards a solution as well.
I'm just not coming up with anything off the top of my head because you essentially need a separate generated table for each agent that you then need to append all together.
Why don't you know how many agents you have? It isn't in the data? Or is that more of a general statement that you have too many agents for this solution to be viable?
Use GENERATE along with a CALENDAR table. So, create a CALENDAR table that is essentially:
Calendar = CALENDAR(MIN([Start]),MAX([End]))
Then use GENERATE. Might have to do some filtering and such, I have some recently posted examples of using GENERATE in the Quick Measures gallery. Otherwise, @Phil_Seamark has the best information on the use of GENERATE that I have seen in his great new book, https://www.amazon.com/Beginning-DAX-Power-BI-Intelligence/dp/1484234766
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |