Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

Need to compare values in same column but different rows

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!!

Hi Anonymous ,

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.

Need to compare values in same column but different rows.pbix26 KB

8 Replies

tamerj1
Community Champion
4 years ago
Anonymous
Would you please provide a copy/paste sample data to work with? Thank you
- Anonymous
  Not applicable
  4 years ago
  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
v-yanjiang-msft
Community Support
4 years ago
Hi Anonymous ,

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.
Need to compare values in same column but different rows.pbix26 KB
- Anonymous
  Not applicable
  4 years ago
  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
    Community Support
    4 years ago
    Hi Anonymous ,
    
    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

Forum Discussion

Need to compare values in same column but different rows

8 Replies

Related content

Comparing values between different tables

Comparing Two Different Datasets for missing values

Date Different return wrong value

Different collor compare values

Compare different tables