Need to compare values in same column but different rows

[Lucas_Phil]

Hello everyone!

I'm having a problem with this situation, would be glad if someone could help me or indicated some DAX function that might help as well.
The problem: If a value is "Yellow" or "Red" I need to check it again in 20 minutes to see if it is still "yellow" or not and then count in my total. My problem is that the values are not one after another, and I need to compare if the specification is the same and if it respects the 20 minutes.

Trying to be more clear, what I have here:
*just to illustrate I made this tables as examples, the real one have way more specifications and I don't think I can share 😕 .
For one product, I have a lot of atributes checked hour by hour and the value of it.

*Note that some measures are made again in the same hour, gonna be explained below.
I have the ranges that are differente for each specification and some formulas that give me the calculated coluns if it is Green/Yellow/Red.

Example:

If the specification is in a green range everything is ok, it count as green for that hour and the measure is not made again.
If it is in a yellow or red range, I need to check the next value for this specification (made in the max of 20min from the first one) and if it is again yellow/red, then I count that hour as yellow/red fot that specification. If the new measure it is not made in 20min, it also counts as yellow/red.
All of this I made in Power BI and it is working pretty well. 

But now, I need to say how many hours the product is green/yellow/red, and that is where I'm struggling. It counts all the results for green/yellow/red:

When what I need is: 

After that, my resume of the day need to be:

Because if I have one specification yellow in that hour, the whole hour is yellow.

If I could write and if/else formula, an example would be:
IF [Result] = "Yellow" AND [Result after 20min] = "Yellow" THEN "Yellow"

But I don't know how to ask to Power BI to check the value of same specification in 'x' minutes.
I tried EARLIER, but not always one value is after another.

Hope I was clear and if you need any more information, please tell me. Thank you!!

[v-yanjiang-msft · Accepted solution]

Hi @Lucas_Phil ,

According to your description, here's my solution.

1. Create a calculated column.

Result =
IF (
    'Table'[Hour]
        = MINX (
            FILTER (
                'Table',
                'Table'[Specification] = EARLIER ( 'Table'[Specification] )
                    && HOUR ( 'Table'[Hour] ) = HOUR ( EARLIER ( 'Table'[Hour] ) )
            ),
            'Table'[Hour]
        ),
    IF (
        'Table'[Green] = 1,
        "Green",
        IF (
            'Table'[Yellow] = 1
                && COUNTROWS (
                    FILTER (
                        'Table',
                        'Table'[Specification] = EARLIER ( 'Table'[Specification] )
                            && DATEDIFF ( EARLIER ( 'Table'[Hour] ), 'Table'[Hour], MINUTE ) <= 20
                            && DATEDIFF ( EARLIER ( 'Table'[Hour] ), 'Table'[Hour], MINUTE ) > 0
                            && 'Table'[Green] = 1
                    )
                ) = 0,
            "Yellow",
            IF (
                'Table'[Red] = 1
                    && COUNTROWS (
                        FILTER (
                            'Table',
                            'Table'[Specification] = EARLIER ( 'Table'[Specification] )
                                && DATEDIFF ( EARLIER ( 'Table'[Hour] ), 'Table'[Hour], MINUTE ) <= 20
                                && DATEDIFF ( EARLIER ( 'Table'[Hour] ), 'Table'[Hour], MINUTE ) > 0
                                && 'Table'[Green] = 1
                        )
                    ) = 0,
                "Red",
                "Green"
            )
        )
    )
)

Result:

2.If you put the columns in the visual and select count, it will get your snapshot result.

Instead, create three measures.

Hours in Green =
CALCULATE ( COUNT ( 'Table'[Result] ), 'Table'[Result] = "Green" )

Hours in Yellow =
CALCULATE ( COUNT ( 'Table'[Result] ), 'Table'[Result] = "Yellow" ) + 0

Hours in Red =
CALCULATE ( COUNT ( 'Table'[Result] ), 'Table'[Result] = "Red" ) + 0

 Then put the Result column and the measures in a visual, get the correct result.

3. Create a color table.

Create two measures.

Total =
SWITCH (
    MAX ( 'Color'[Color] ),
    "Green", 'Table'[Hours in Green],
    "Yellow", 'Table'[Hours in Yellow],
    "Red", 'Table'[Hours in Red]
)

% of Total =
DIVIDE ( 'Color'[Total], COUNT ( 'Table'[Result] ) )

Get the correct result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Lucas_Phil]

Good morning @v-yanjiang-msft! 

Thank you very much, helped me a lot. I was going in the same way, with "EARLIER" function, but your idea with the "IF" + "MINX" + "COUNTROWS" is going to save me some calculated columns, thanks!

Just one addition and one more question:

I didn't put in the sample table the date column with the day of the measure, thought it was irrelevant, but in the original one I have, and because of that I had to add an "EARLIER" for the date, as you can see below:

IF (
            'Table'[Yellow] = 1
                && COUNTROWS (
                    FILTER (
                        'Table',
                        'Table'[Specification] = EARLIER ( 'Table'[Specification] )
                            && DATEDIFF ( EARLIER ( 'Table'[Hour] ), 'Table'[Hour], MINUTE ) <= 20
                            && DATEDIFF ( EARLIER ( 'Table'[Hour] ), 'Table'[Hour], MINUTE ) > 0
                            && 'Table'[Green] = 1
                            && EARLIER ( 'Table'[Date] ) =  'Table'[Date] 
                            
                    )
                ) = 0,
            "Yellow",

The question is: 
The % of total is working when I filt with the specification, but how can I do when I don't have any filter?
Because in the end of the day, I need to now how many hours were green and yellow, so the result without any filter at specification should be: 

For this example, happens to be the same for the specification of Salt, but is not always like these. 
Let's say Sugar and Salt were yellow in the same hour: in the end of the day, we would have one hour in yellow and the rest in green.
Let's say Sugar and Salt were yellow in two different hours: in the end of the day, we would have two hours in yellow and the rest in green.
Let's say Salt have two hours in yellow (12h and 13h) and Sugar one (13h): in the end of the day, we would have two hours in yellow and the rest in green.
Let's say Salt have two hours in yellow (12h and 13h) and Sugar one (14h): in the end of the day, we would have three hours in yellow and the rest in green.

Do you have any idea on how it would work?
I tried putting a filter inside the "Total" measure for "Specification" but nothing happened.

Was I clear about the issue? If need anything more, please let me know. 
And again, already helped a lot, thanks!

[v-yanjiang-msft]

Hi @Lucas_Phil ,

It's my pleasure!

But I'm not clear about the result if there's no filter with the specification, from the data can see, if we ignore the specification, the total amount should be green-8 and  yellow-2, not green-3 and yellow-2.

Maybe I missed something? Could you please clarify it more?

Looking forward to your reply!

Best Regards,
Community Support Team _ kalyj

[Lucas_Phil]

Ok, gonna try to clarify it!

For this part of the process we need to know the general result of the hour. So it's almost like we have one more variable, I'm going to call it as GeneralResult.
If any specification got "red" in that hour, the GeneralResult is "red".
If any is "yellow", GeneralResult is "yellow".
If the results for all the specifications are "green", just then we have the GeneralResult as "green".

GeneralResult would be something like this but for the whole hour:

GeneralResult = 
IF ( [Result] in { "Red" }, 
"Red",
   IF ( [Result] in { "Yellow" }, 
   "Yellow",
      IF ( [Result] in { "Green" },
      "Green
      )
   )
)

The formula above doesn't work of course because is counting everything in the column, not caring if it is the hour or not.


I think we could say that first we had to look for each specification by hour, and now we need to look for each hour by the result of all the specifications.

Do I clarified it? Or make it worse?

[Lucas_Phil]

Hi @v-yanjiang-msft!
I solved in a way that maybe is not the most correct one, but it is working.

I create three measures for each color, gonna show here:

Counts Red = 

VAR __countsRed = CALCULATE(
	COUNTA('Table'[Hour]),
	'Table'[Result] IN { "Red" }
)

RETURN IF ( __countsRed >= 1, 1, 0)

The "IF" part ensures that it will give us only one per hour. The two formulas below are for the total/hour and total/day (we don't have [date] field in the sample table, but in the original I have, that's why it's here).

Counts Red_Hour = 

VAR __Total = SUMMARIZE(Table,Table[Hour],"__total", Table[Counts Red])

RETURN IF(HASONEVALUE(Table[Hour]),Table[Counts Red], SUMX(__Total,Table[Counts Red]))

Counts Red_Day = 

VAR __Total = SUMMARIZE(Table,Table[Data],"__total", Table[Counts Red_Hour])

RETURN IF(HASONEVALUE(Table[Data]),Table[Counts Red_Hour], SUMX(__Total,Table[Counts Red_Hour]))

For yellow and green it follows the same logic, but I added one differente thing:

Counts Yellow = 

VAR __countsYellow = CALCULATE(
	COUNTA('Table'[Hour]),
	'Table'[Result] IN { "Yellow" }
)

RETURN IF (Table[Counts Red] = 0 && __countsYellow >= 1, 1, 0)

for the green count, it is Table[Counts Yellow] = 0.

In the end:

Well, not sure if it is the best way of calculate it, but it is working pretty well! 

If you have any advice on it, please feel freely!
Already helped me a lot with the first solution, so I will wait a couple days before accept that and maybe close the topic, thanks!

[Lucas_Phil]

Hi! Back to this topic again 🙂

It worked as it should, but it is taking so long to refresh and now I have memory issues and the message "Resources Exceeded" for some pages 😞

Sorry to bother again with this @v-yanjiang-msft and @tamerj1, but would you have a tip or an idea to make it use less memory? 
In some research and analysing all the code I believe that SUMMARIZE is probably what is taking the biggest memory rate, do you know an alternative to it?
An idea it is to create a new table from the ones I have, only with the columns we are using inside the SUMMARIZE function, what do you think?

The measures are exactly what is here, but replicated in another tables.

Thank you in advance!

[tamerj1]

@Lucas_Phil 
Would you please provide a copy/paste sample data to work with? Thank you

[Lucas_Phil]

Here it is!

Sorry for taking so long, I was trying to recreate the best I could to give you a good sample. 

https://drive.google.com/drive/folders/1zRINBD_hmR_2welTG6fpaFlAmWkhPCvS?usp=sharing