The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Is it possible to generate a series on DAX where my starting and ending values are datetime and I want to generate a series for the same time on different days.
i.e. starting on
1/1/2021 4:00:00 PM |
ending on
1/5/2021 4:00:00 PM |
and I want DAX to generate the following
| Custom |
|---------------------|
| 1/1/2021 4:00:00 PM |
| 1/2/2021 4:00:00 PM |
| 1/3/2021 4:00:00 PM |
| 1/4/2021 4:00:00 PM |
| 1/5/2021 4:00:00 PM |
An equivalent power queryquery output is following
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJUMLEyMAAihQBfJR2gqCmGaGwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [d1 = _t, d2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"d1", type datetime}, {"d2", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.DateTimes([d1],Duration.Days([d2] - [d1])+1,#duration(1,0,0,0))),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
#"Expanded Custom"
If I have a table X as following
| Column1 | Column2 |
|---------------------|---------------------|
| 1/1/2021 4:00:00 PM | 1/5/2021 4:00:00 PM |
with following 2 measures
Measure 2 = MIN([Column1])
Measure 3 = MAX([Column2])
and I try GENERATESERIES as following
Table 2 = GENERATESERIES([Measure 2],[Measure 3],5)
it does not give me what I want
How can I achieve this in DAX?
Solved! Go to Solution.
Table 3 = GENERATESERIES(SELECTCOLUMNS('Table 2',"min",'Table 2'[d1]),SELECTCOLUMNS('Table 2',"max",'Table 2'[d2]))
Table 3 = GENERATESERIES(SELECTCOLUMNS('Table 2',"min",'Table 2'[d1]),SELECTCOLUMNS('Table 2',"max",'Table 2'[d2]))