Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all,
I have a table with columns Time, Index, Shift and Value like so:
Time | Index | Shift | Value |
7:00 | 1 | Day | 0 |
8:00 | 2 | Day | 32 |
9:00 | 3 | Day | 34 |
10:00 | 4 | Day | 68 |
11:00 | 5 | Day | 72 |
12:00 | 6 | Day | 0 |
13:00 | 7 | Day | 35 |
14:00 | 8 | Day | 40 |
15:00 | 9 | Day | 50 |
16:00 | 10 | Day | 60 |
17:00 | 11 | Day | 10 |
18:00 | 12 | Day | 0 |
19:00 | 13 | Night | 0 |
20:00 | 14 | Night | 0 |
21:00 | 15 | Night | 0 |
22:00 | 16 | Night | 70 |
23:00 | 17 | Night | 60 |
0:00 | 18 | Night | 50 |
1:00 | 19 | Night | 70 |
2:00 | 20 | Night | 0 |
3:00 | 21 | Night | 80 |
4:00 | 22 | Night | 0 |
5:00 | 23 | Night | 0 |
6:00 | 24 | Night | 0 |
I need to write some measures to return the red highlighted values like so:
1. The time of the first non-zero value where shift = day (8:00)
2. The time of the first continuous zero value through to the end of the shift where shift = day (18:00)
3. The time of the first non-zero value where shift = night (22:00)
4. The time of the first continuous zero value through to the end of the shift where shift = night (4:00)
Appreciate the help!
Thank you.
Solved! Go to Solution.
Hi @ausphil
Try this for your 4th requirement i,e. The time of the first continuous zero value through to the end of the shift where shift = night (4:00).
You can use same logic for 2nd requirement
Measure4 = VAR LastNonZeroNight = MAXX ( TOPN ( 1, FILTER ( Table1, [Shift] = "Night" && [Value] <> 0 ), [Index], DESC ), [Index] ) RETURN MINX ( TOPN ( 1, FILTER ( Table1, [Shift] = "Night" && [Value] = 0 && [Index] > LastNonZeroNight ), [Index], ASC ), [Time] )
For first and third requirment, you could try this MEASURE's pattern
Measure 1 = MINX ( TOPN ( 1, FILTER ( Table1, [Shift] = "Day" && [Value] <> 0 ), [Index], ASC ), [Time] )
@Zubair_Muhammad wrote:
For first and third requirment, you could try this MEASURE's pattern
Measure 1 = MINX ( TOPN ( 1, FILTER ( Table1, [Shift] = "Day" && [Value] <> 0 ), [Index], ASC ), [Time] )
Thank you @Zubair_Muhammad this one works perfectly!
Just change day to night or vice versa for similar 2nd and 3rd MEASURE
Hi @ausphil
Try this for your 4th requirement i,e. The time of the first continuous zero value through to the end of the shift where shift = night (4:00).
You can use same logic for 2nd requirement
Measure4 = VAR LastNonZeroNight = MAXX ( TOPN ( 1, FILTER ( Table1, [Shift] = "Night" && [Value] <> 0 ), [Index], DESC ), [Index] ) RETURN MINX ( TOPN ( 1, FILTER ( Table1, [Shift] = "Night" && [Value] = 0 && [Index] > LastNonZeroNight ), [Index], ASC ), [Time] )
@Zubair_Muhammad wrote:Hi @ausphil
Try this for your 4th requirement i,e. The time of the first continuous zero value through to the end of the shift where shift = night (4:00).
You can use same logic for 2nd requirement
Measure4 = VAR LastNonZeroNight = MAXX ( TOPN ( 1, FILTER ( Table1, [Shift] = "Night" && [Value] <> 0 ), [Index], DESC ), [Index] ) RETURN MINX ( TOPN ( 1, FILTER ( Table1, [Shift] = "Night" && [Value] = 0 && [Index] > LastNonZeroNight ), [Index], ASC ), [Time] )
Thank you @Zubair_Muhammad this seems to work for the 4th requirement, but for the 1st requirement it is returning 7:00 AM. Any thoughts?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |