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"
    )
smpa01's avatar
smpa01
Community Champion
4 years ago

philosophie_e  you can create a calculated column inT1 like this and use that as legend

 

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"
    )

 

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"
    )