Forum Discussion
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 ...
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 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"
)
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?