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

    Hello!

    It seems like it is working, thank you very much! Now my next struggle, which I realised, is that I cannot use a measure in my map visual where I wanted to use the output as a Legend. Any suggestions here?
     

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