DAX Formula to retrieve a Field value

[ovetteabejuela]

Hi Everyone,

I'm in need of your help to formulate a DAX function to retrieve a field value. The image below should be able to describe what I wanted to achieve.

EDIT: please assume that the table on the left is name [PLACES], and on the right is [DAILYROAM], I wanted to apply table names in the formula that you will share, thanks for the help. I just realized I can't attach a document please see the tables below.

Where I'm At	From This Day	To This Day
Alaska	1/2/2017	1/6/2017
Brazil	1/7/2017	1/9/2017

 

 
Where I'm at on this day	The date is
Alaska	1/2/2017
Alaska	1/3/2017
Alaska	1/4/2017
Alaska	1/5/2017
Alaska	1/6/2017
Brazil	1/7/2017
Brazil	1/8/2017
Brazil	1/9/2017

[ElliotP]

If 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...

[ovetteabejuela]

Sorry for not being so specific, basically here's what I truly want:

[Phil_Seamark · Accepted solution]

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]

[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!

[ovetteabejuela]

Hi @v-shex-msft & @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!

[v-shex-msft]

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