Forum Discussion

ovetteabejuela's avatar
ovetteabejuela
Impactful Individual
9 years ago
Solved

DAX Formula to retrieve a Field value

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
Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    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.

6 Replies

  • ElliotP's avatar
    ElliotP
    Post Prodigy
    9 years ago

    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's avatar
      ovetteabejuela
      Impactful Individual
      9 years ago

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

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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