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"
    )
sevenhills's avatar
sevenhills
Super User
4 years ago

Please refine:

 

a) Could you go to Excel, create sample data for each table and columns you have. copy paste here. 

b) Similarly what is the output expected for the conditions, so that it helps.

  • philosophie_e's avatar
    philosophie_e
    Helper I
    4 years ago

    Hello!

    I created a sample file, please have a look. I would like to use the output value column as a legend in the map visual (see above).

    Thank you.