Forum Discussion
IF
Post Prodigy
Post Prodigygrouping data and visualization
Hi, I have an interesting scenario for grouping . I want to group my data in the following way: I have two different columns. One of them is for "On time", late, early. The other column gives...
if i understand you correctly you need to create a column like this; (you will need to fill in all your scenarios i have only done a few)
if you want them sorted in a certains sequence you can create 2 switch statements with the sequence and order by the sequenceHoursStatus =
SWITCH (
TRUE (),
'Table'[Status] = "On time", "On Time",
'Table'[Status] = "Early"
&& 'Table'[Hours] = 1, "+1 Hour",
'Table'[Status] = "Early"
&& 'Table'[Hours] = 2, "+2 Hour",
'Table'[Status] = "Late"
&& 'Table'[Hours] = 1, "-1 Hour",
'Table'[Status] = "Late"
&& 'Table'[Hours] = 2, "-2 Hour",
"Unknown"
)HoursStatusSequence =
SWITCH (
TRUE (),
'Table'[Status] = "On time", 3,
'Table'[Status] = "Early"
&& 'Table'[Hours] = 1, 4,
'Table'[Status] = "Early"
&& 'Table'[Hours] = 2, 5,
'Table'[Status] = "Late"
&& 'Table'[Hours] = 1, 2,
'Table'[Status] = "Late"
&& 'Table'[Hours] = 2, 1,
0
)
vanessafvg
Community Champion
Community Championif i understand you correctly you need to create a column like this; (you will need to fill in all your scenarios i have only done a few)
if you want them sorted in a certains sequence you can create 2 switch statements with the sequence and order by the sequence
HoursStatus =
SWITCH (
TRUE (),
'Table'[Status] = "On time", "On Time",
'Table'[Status] = "Early"
&& 'Table'[Hours] = 1, "+1 Hour",
'Table'[Status] = "Early"
&& 'Table'[Hours] = 2, "+2 Hour",
'Table'[Status] = "Late"
&& 'Table'[Hours] = 1, "-1 Hour",
'Table'[Status] = "Late"
&& 'Table'[Hours] = 2, "-2 Hour",
"Unknown"
)
SWITCH (
TRUE (),
'Table'[Status] = "On time", "On Time",
'Table'[Status] = "Early"
&& 'Table'[Hours] = 1, "+1 Hour",
'Table'[Status] = "Early"
&& 'Table'[Hours] = 2, "+2 Hour",
'Table'[Status] = "Late"
&& 'Table'[Hours] = 1, "-1 Hour",
'Table'[Status] = "Late"
&& 'Table'[Hours] = 2, "-2 Hour",
"Unknown"
)
HoursStatusSequence =
SWITCH (
TRUE (),
'Table'[Status] = "On time", 3,
'Table'[Status] = "Early"
&& 'Table'[Hours] = 1, 4,
'Table'[Status] = "Early"
&& 'Table'[Hours] = 2, 5,
'Table'[Status] = "Late"
&& 'Table'[Hours] = 1, 2,
'Table'[Status] = "Late"
&& 'Table'[Hours] = 2, 1,
0
)
SWITCH (
TRUE (),
'Table'[Status] = "On time", 3,
'Table'[Status] = "Early"
&& 'Table'[Hours] = 1, 4,
'Table'[Status] = "Early"
&& 'Table'[Hours] = 2, 5,
'Table'[Status] = "Late"
&& 'Table'[Hours] = 1, 2,
'Table'[Status] = "Late"
&& 'Table'[Hours] = 2, 1,
0
)