Define a category column in a table visual

Hi amaleranda 

First create a time table in Edit queries

let
    Source = List.Times(#time(0, 0, 0), 60*24, #duration(0, 0, 1, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each List.Dates(#date(2019, 9, 1), 5, #duration(1, 0, 0, 0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Sorted Rows" = Table.Sort(#"Expanded Custom",{{"Custom", Order.Ascending}, {"Column1", Order.Ascending}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Sorted Rows", {{"Custom", type text}, {"Column1", type text}}, "en-US"),{"Custom", "Column1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"date time"),
    #"Changed Type" = Table.TransformColumnTypes(#"Merged Columns",{{"date time", type datetime}})
in
    #"Changed Type"

Close &&apply

Create a caluclated column in time table

date = [date time].[Date]

Don't create relationship between this time table and your table

 

Create measures on your table

start = CALCULATE(MIN('Table'[local date time]),FILTER(ALLEXCEPT('Table','Table'[Equip]),'Table'[local date time].[Date]=SELECTEDVALUE('time table'[date])))

end = CALCULATE(MAX('Table'[local date time]),FILTER(ALLEXCEPT('Table','Table'[Equip]),'Table'[local date time].[Date]=SELECTEDVALUE('time table'[date])))


A = MIN('time table'[date time])

B = MAX('time table'[date time])

Category = SWITCH(TRUE(),[start]=[A]&&[end]=[B],"category1",[start]>[A]&&[end]<[B],"category2",[start]=[A]&&[end]<[B],"category3",[start]>[A]&&[end]=[B],"category4")

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.