Forum Discussion
How to build condition?
Hi all
I have in formula this condition:
FILTER
(CROSSJOIN(Downtime;'HourOfDay');
HOUR(Downtime[Start])<='HourOfDay'[Hour of Day]&&HOUR
(Downtime[End])>='HourOfDay'[Hour of Day]
)I need to add additnal lines to make it working as I want, but don't know how to build DAX formula.
I want to add those lines into filter table.
Many thanks
Michal
DAY(Downtime[Start])<>DAY(Downtime[End]) then show lines where HourOfDay'[Hour of Day]>=HOUR(Downtime[Start])
plus
DAY(Downtime[Start])<>DAY(Downtime[End]) then show lines where HourOfDay'[Hour of Day]<=HOUR(Downtime[End])
Just use the logical OR operator (||) to combine expressions.
https://docs.microsoft.com/en-us/dax/dax-operator-reference#logical-operators
4 Replies
Hi MichalSimota
Is this correct? You are showing the same (in red) on both conditions
DAY(Downtime[Start])<>DAY(Downtime[End]) then show lines where HourOfDay'[Hour of Day]>=HOUR(Downtime[Start])
plus
DAY(Downtime[Start])<>DAY(Downtime[End]) then show lines where HourOfDay'[Hour of Day]<=HOUR(Downtime[End])Hi
What I need to achieve by this is to show downtime for each hour(not just for start or end hour) so I need to filter all lines which are between start and end hour. It's done by fist formula:
HOUR(Downtime[Start])<='HourOfDay'[Hour of Day]&&HOUR (Downtime[End])>='HourOfDay'[Hour of Day]
But because some downtimes continue from one day to another, just this formula is not enough for such downtimes( when it start at 23:00 and finish at 01:00) So for those cases I need to add condittion to see also those hours. My idea was to add those conditions, so I tried it several times to build fomula but it did not work.
So Idea was to add comparison of days: DAY(Downtime[Start])<>DAY(Downtime[End]) and if this condition is walid, than I need to filter lines wich have this HourOfDay'[Hour of Day]>=HOUR(Downtime[Start]) or this HourOfDay'[Hour of Day]<=HOUR(Downtime[End]) condition.
Thanks
Michal
Can you show the structure of your tables 'Downtime' and 'Hour of Day'? or share the pbix?
It'd be easier to come up with a solution with that info
