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.

v-yanjiang-msft's avatar
v-yanjiang-msft
Icon for Community Support rankCommunity 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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

 

 

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?

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