Define a category column in a table visual

[amaleranda]

Hi I have created below visual based on a datased that has below columns

[Equip], [LocalDateTime],[Avialble]. Data set is at the minute level granuality. Avialble can be 'Ture' or 'False'

I have created below measures 

Start := MIN([LocalDateTime])

End :=MAX(LocalDateTime])

Duration := DISTINCTCOUNT([LocalDateTime])

For time Slicer I use TimeBrush (custome visual) and a Table.

I need to create a measure for category so that I can put on the same tabel visual. 

The logic should be based on the time slicer selection, When user is select a time window from the time brush visual. [Start], [End] and [Duration] gets calculated accordingly.  What I need in my cattegory measure is below logic

Say

Selected Start Time = A ; Selected End Time = B (from time brush visual)

when [Start] = A and [End] = B Then 'Category1'

when [Start]>A and [End] < B Then 'Category2'

when [Sart] = A and [End] < B then 'Category3'

when[Start] > A and End = B then 'Category4'

Your help would be massively appriciated.

[v-juanli-msft]

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.

[amaleranda · Accepted solution]

Hi  v-juanli-msft, 

This could work,

But I do not want to include a new calcualted tabel into the model. 

I was able to achive the result I reuired by altering the FILTER CONTEXT by using calcualte. It was simply removing the filter of equipment code to include all the [DateTime] in the OUTER FILTER CONTEXT of the tabel. 

My measure gose like below.

CategoryDescription =:

VAR OuterStartTime = CALCULATE(MIN(EquipmentAvailability[LocalDateTime]),ALL(EquipmentAvailability[EquipmentCode]))

VAR OuterEndTime = CALCULATE(MAX(EquipmentAvailability[LocalDateTime]),ALL(EquipmentAvailability[EquipmentCode]))

VAR Category =

SWITCH(TRUE(),

[StartTime] > OuterStartTime && [Endtime] < OuterEndTime ,"G1",

[StartTime] <= OuterStartTime && [Endtime] >= OuterEndTime,"G2",

[StartTime] <= OuterStartTime && [Endtime] < OuterEndTime, "G3",

[StartTime] > OuterStartTime && [Endtime] >= OuterEndTime, "G4"

)

RETURN

Category

And the Output.

Can I call myself now DAX Intermediate lol.

Thanks a lot for your help.