Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi Team,
I have trouble transforming Table A to the Output Calculated Table . I wanted for every category and based on Min and Max dates, i want to generate consecutive dates between Min and Max for the Category field. Is it possible to do in DAX?
I could have done in M query, but then Table A is derived using DAX and moving back to power query and rebuilding there is a tough work.
Thank you
Table A:
A | Min | Max |
Apple | 3/1/2023 | 3/5/2023 |
Orange | 2/1/2023 | 2/5/2023 |
Banana | 1/1/2023 | 1/5/2023 |
Output Calculated Table:
A | Date |
Apple | 3/1/2023 |
Apple | 3/2/2023 |
Apple | 3/3/2023 |
Apple | 3/4/2023 |
Apple | 3/5/2023 |
Orange | 2/1/2023 |
Orange | 2/2/2023 |
Orange | 2/3/2023 |
Orange | 2/4/2023 |
Orange | 2/5/2023 |
Banana | 1/1/2023 |
Banana | 1/1/2023 |
Banana | 1/3/2023 |
Banana | 1/4/2023 |
Banana | 1/5/2023 |
Solved! Go to Solution.
Hey @anvikuttu ,
using GENERATE and GENERATESERIES does the trick:
Table 2 =
SELECTCOLUMNS(
GENERATE(
'Table'
, var startValue = [Min]
var endValue = [Max]
return
GENERATESERIES( startValue , endValue , 1 )
)
, "A" , [A]
, "Date" , [Value]
)
A screenshot:
Hopefully, this provides what you are looking for.
Nevertheless, you must consider that DAX tables will not benefit from all compressions that are happening during data load/data refresh. If the table becomes large, you might encounter performance degradation when this table is used inside measures.
Regards,
Tom
hi @anvikuttu
you would need a date table to help, try create one like:
Dates = CALENDAR(MIN(TableA[Min]), MAX(TableA[Max]))
then write a calculated table like:
Table2 =
GENERATE(
VALUES(TableA[A]),
VAR MinDate = CALCULATE(MIN(TableA[Min]))
VAR MaxDate = CALCULATE(MAX(TableA[MAX]))
RETURN
CALCULATETABLE(
VALUES(Dates[Date]),
Dates[Date]>=MinDate,
Dates[Date]<=MaxDate
)
)
it worked like:
Hey @anvikuttu ,
using GENERATE and GENERATESERIES does the trick:
Table 2 =
SELECTCOLUMNS(
GENERATE(
'Table'
, var startValue = [Min]
var endValue = [Max]
return
GENERATESERIES( startValue , endValue , 1 )
)
, "A" , [A]
, "Date" , [Value]
)
A screenshot:
Hopefully, this provides what you are looking for.
Nevertheless, you must consider that DAX tables will not benefit from all compressions that are happening during data load/data refresh. If the table becomes large, you might encounter performance degradation when this table is used inside measures.
Regards,
Tom
Thank you @TomMartens , I agree with your statement. In my case the data is not much.
tried to imitating TomMartens' solution:
Table4 =
GENERATE(
VALUES(TableA[A]),
VAR MinDate = CALCULATE(MIN(TableA[Min]))
VAR MaxDate = CALCULATE(MAX(TableA[MAX]))
RETURN
GENERATESERIES(MinDate, MaxDate)
)
Hi @anvikuttu
You can achieve it with power query.
Please refer to attached link:
https://www.thepoweruser.com/2019/07/23/fill-dates-between-dates-with-power-bi-power-query/
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thank you, however as mentioned in my msg i would like to do this in DAX
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
88 | |
86 | |
83 | |
65 | |
49 |
User | Count |
---|---|
127 | |
108 | |
88 | |
70 | |
66 |