Forum Discussion

Anonymous's avatar
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 "Re...
Need Help
Tips and Tricks
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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!

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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!