Forum Discussion

philosophie_e's avatar
philosophie_e
Helper I
4 years ago
Solved

MAX with condition

Hello, I need some support in establishing status for countries using MAX function (or potentially any other). I have three tables: Table 1 with name of countries where I want to have the final ...
Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    philosophie_e 

    Column = 
VAR _status =
    CALCULATE(COUNT ( Table2[Status] ),ALLEXCEPT(Table1,Table1[Country]))
VAR _live =
    CALCULATE ( COUNT ( Table2[Status] ), Table2[Status] = "Live" )
VAR _ongoing =
    CALCULATE ( COUNT ( Table2[Status] ), Table2[Status] = "Ongoing" )
VAR _scheduled =
    CALCULATE ( COUNT ( Table2[Status] ), Table2[Status] = "Scheduled" )
RETURN
    SWITCH (
        TRUE (),
        _live <> BLANK ()
            && _live = _status, "Fully Live",
        _live <> BLANK ()
            && _live <> _status, "Partially Live",
        _ongoing <> BLANK ()
            && _ongoing = _status, "Ongoing",
        _scheduled <> BLANK ()
            && _scheduled = _status, "Scheduled","Not Live"
    )
philosophie_e's avatar
philosophie_e
Helper I
4 years ago

Thank you! It works like a charm, except for one case. I found one outlier that has blank cell in a calculated column:
Power BI calculated column:

 


Source data:

What is causing such a behaviour?
Thank you 🙂 

 

smpa01's avatar
smpa01
Community Champion
4 years ago

philosophie_e  provide t1 and t2 data only for JAPAN

  • philosophie_e's avatar
    philosophie_e
    Helper I
    4 years ago

    Okay, I think I know what is the issue.... Japan has two company codes that have statuses "Scheduled" and "Ongoing"... Meaning that the final outcome cannot be one out of the final list I had provided. It should be "Not live". I need to add one more entry in my calculated column to get those cases showing. Correct?

    • smpa01's avatar
      smpa01
      Community Champion
      4 years ago

      philosophie_e 

      Column = 
VAR _status =
    CALCULATE(COUNT ( Table2[Status] ),ALLEXCEPT(Table1,Table1[Country]))
VAR _live =
    CALCULATE ( COUNT ( Table2[Status] ), Table2[Status] = "Live" )
VAR _ongoing =
    CALCULATE ( COUNT ( Table2[Status] ), Table2[Status] = "Ongoing" )
VAR _scheduled =
    CALCULATE ( COUNT ( Table2[Status] ), Table2[Status] = "Scheduled" )
RETURN
    SWITCH (
        TRUE (),
        _live <> BLANK ()
            && _live = _status, "Fully Live",
        _live <> BLANK ()
            && _live <> _status, "Partially Live",
        _ongoing <> BLANK ()
            && _ongoing = _status, "Ongoing",
        _scheduled <> BLANK ()
            && _scheduled = _status, "Scheduled","Not Live"
    )
      • philosophie_e's avatar
        philosophie_e
        Helper I
        4 years ago

        Hello!

        The code is working perfectly well. Now I am trying to introduce some changes to the formula, namely in this place:

                _live <> BLANK ()
            && _live = _status, "Fully Live",
        _live <> BLANK ()
            && _live <> _status, "Partially Live",

        Now I have two new statuses that should also resemble "Live". 
        Meaning that if there is a combination of status "Live" and one of those two statuses, I should get "Fully live" (meaning that those statuses presuppose live status). 
        If there is combination of new statuses with "Ongoing"/"Scheduled", then the end result should be "Ongoing"/"Scheduled".  I additionally created two new variables for this purpose: new1 and new2.

        I tried to add it as (1):
        _live <> blank ()
                && (_live || _new1 || _new2) = _status, "Fully live",

        but then all values "Partially live" are getting overwritten with other values...

        I also tried adding conditions separately with but it also did not work (2):
        _new1<> blank ()
                && _new1 = _status, "Fully live", 

        but it didn't work due to "<>".

                _live <> BLANK ()
            && _live <> _status, "Partially Live",


        How can I adjust the current formula to add two new statuses that will present "live" status.

        Thank you!