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  what if one country has both ongoing and schedule but no Live...what do you assign at that time ? IF Ongoing and Scheduled status are mutually exclusive but Live, Ongoing,Scheduled are mutually inclusive, the following will work.

 

 

Measure =
VAR _status =
    COUNT ( Table2[Status] )
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

I want to use the outcome of the column as a legend of the map

  • 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