Forum Discussion

dujhe's avatar
dujhe
Regular Visitor
6 years ago
Solved

Create a table with loops

Hi,   I have a dataset of completed responses to a web form which is completed by our providers. Within the dataset is the providers unique ID, the date the form was completed and the value I need ...
Need Help
  • AlB's avatar
    AlB
    6 years ago

    Hi dujhe 

    Agree resorting to M is an option worth considering. In this case, however, the required DAX code is not excessively complex. Create a calculated table; Table1 is the initial table you show:

    NewTable = 
VAR startDate_ = MIN(Table1[Date])
VAR endDate_ = TODAY()
VAR tab0_ = CROSSJOIN(DISTINCT(Table1[ID]),CALENDAR(startDate_,endDate_))
RETURN 
ADDCOLUMNS(tab0_, "Value", 
            VAR latestDate_ = CALCULATE(MAX(Table1[Date]),Table1[Date] <=EARLIER([Date]), Table1[ID] = EARLIER([ID]))
            RETURN CALCULATE(MAX(Table1[Value]),Table1[Date] = latestDate_, Table1[ID] = EARLIER([ID])))

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

AlB's avatar
AlB
Community Champion
6 years ago

Hi dujhe 

Agree resorting to M is an option worth considering. In this case, however, the required DAX code is not excessively complex. Create a calculated table; Table1 is the initial table you show:

NewTable = 
VAR startDate_ = MIN(Table1[Date])
VAR endDate_ = TODAY()
VAR tab0_ = CROSSJOIN(DISTINCT(Table1[ID]),CALENDAR(startDate_,endDate_))
RETURN 
ADDCOLUMNS(tab0_, "Value", 
            VAR latestDate_ = CALCULATE(MAX(Table1[Date]),Table1[Date] <=EARLIER([Date]), Table1[ID] = EARLIER([ID]))
            RETURN CALCULATE(MAX(Table1[Value]),Table1[Date] = latestDate_, Table1[ID] = EARLIER([ID])))

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

AlB's avatar
AlB
Community Champion
6 years ago

dujhe 

another version in M. I think it is likely the version in DAX will be faster (if your tables are large). BaseTable1 is the first table you showed

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIy0Dcw1TcyMDIAcgyVYnUgwsaYwkYg1cZIwkZgYWMQywhJ2BgsbAISNsVqtpEZqiGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type date}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"AvailableDates", each Table.FromColumns({[Date],[Value]}, {"Date", "Value"}) }}),
    allDates_ = List.Dates(List.Min(BaseTable1[Date]), Number.From(Date.From(DateTime.LocalNow()) - List.Min(BaseTable1[Date])) +1,#duration(1,0,0,0)),
    #"Added Column" = Table.AddColumn(#"Grouped Rows", "Missingdates", each let missingdatesL_= List.Difference(allDates_,[AvailableDates][Date]) in Table.FromColumns({missingdatesL_,List.Repeat({null},List.Count(missingdatesL_))}, {"Date", "Value"}) ),
    #"Added Custom" = Table.AddColumn(#"Added Column", "AllDates", each Table.Sort(Table.Combine({[AvailableDates],[Missingdates]}),{{"Date", Order.Ascending}})),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Filldown", each Table.FillDown([AllDates],{"Value"})),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"AvailableDates", "Missingdates", "AllDates"}),
    #"Expanded Filldown" = Table.ExpandTableColumn(#"Removed Columns1", "Filldown", {"Date", "Value"}, {"Date", "Value"})
in
    #"Expanded Filldown"

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers