Forum Discussion
Impactful IndividualDAX Formula to retrieve a Field value
In DAX you can do it this way :
DailyRoam = SELECTCOLUMNS( FILTER( CROSSJOIN('Dates','Places') , 'Places'[From This Day] <= 'Dates'[Date] && 'Places'[To This Day] >= 'Dates'[Date] ), "Where I'm at on this day",'Places'[Where I'm At], "The date is" , Dates[date])But it does rely on you having a [Dates] table which if missing can be generated like this as a new table
Dates = CALENDARAUTO()
You don't need any relationships.
Post ProdigyIf you have the Dailyroam table in your pbix file then you should be able to simply grab a matrix visual (or a table visual) and drag both "Where I'm at on this day" and the "the date is" columns and they should line up.
I feel we're missing something though...
Impactful IndividualSorry for not being so specific, basically here's what I truly want:
Hi ovetteabejuela,
I think power query can achieve your requirement, you can refer to below formula to expand the date range.
Original Table: Name, Start date, End date.
1. Add a custom column to generate the list of date range.
List.Dates([StartDate], Duration.Days(DateTime.Date([EndDate])-DateTime.Date([StartDate])), #duration(1, 0, 0, 0))
2. Convert above list to table.
Table.FromList(List.Dates([StartDate], Duration.Days(DateTime.Date([EndDate])-DateTime.Date([StartDate])), #duration(1, 0, 0, 0)),Splitter.SplitByNothing(), null, null, ExtraValues.Error)
3. Select Name column and the column with store the tables, then expand these columns.
Full query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLUN9U3MjA0BTMNDSDsWJ1oJSegiJG+GUjADMg0gTFBcs5AAWN9I5hGIBOmMRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, StartDate = _t, EndDate = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"StartDate", type date}, {"EndDate", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Range", each Table.FromList(List.Dates([StartDate], Duration.Days(DateTime.Date([EndDate])-DateTime.Date([StartDate])), #duration(1, 0, 0, 0)),Splitter.SplitByNothing(), null, null, ExtraValues.Error)), #"Expanded Range" = Table.ExpandTableColumn(Table.SelectColumns(#"Added Custom",{"Name","Range"}), "Range", {"Column1"}, {"Range.Column1"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded Range",{{"Range.Column1", "Date"}}) in #"Renamed Columns"Regards,
Xiaoxin sheng
- Phil_Seamark
Microsoft Employee
In DAX you can do it this way :
DailyRoam = SELECTCOLUMNS( FILTER( CROSSJOIN('Dates','Places') , 'Places'[From This Day] <= 'Dates'[Date] && 'Places'[To This Day] >= 'Dates'[Date] ), "Where I'm at on this day",'Places'[Where I'm At], "The date is" , Dates[date])But it does rely on you having a [Dates] table which if missing can be generated like this as a new table
Dates = CALENDARAUTO()
You don't need any relationships.
- ovetteabejuela
Hi Anonymous & Phil_Seamark,
Thank you both for sharing and helping out I will try both of your solutions though I was primarily looking for the DAX version for PowerBI like Phil_Seamark has shared but I would be happy to see how the PowerQuery would also fair.
Thanks again!
- ovetteabejuela
[update] Okay, I would say as a newbie that I'm very much impressed and happy with your solution Phil_Seamark, it's just perfect!