Forum Discussion
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 output. The countries do not repeat (one Russia, one Australia).
Table 2 with country company codes and their go-live statuses. One country can have multiple company codes (Russia has only one, Autstralia has five different company codes). Each ompany code has status "Live", "Ongoing", "Scheduled": company code in Russia is in status "Ongoing", in Australia two company codes have status "Live", one has "Ongoing" and last one is "Scheduled".
Index table 3 with output statuses that I need to show in table 1: (1) Fully live, (2) Partially live, (3) Ongoing, (4) Scheduled.
The goal is to show status of country by company code in Table 1 using Table 3:
1) If in one country all company codes are live, then the new status in table 1) is (1) Fully live.
2) If in one country not all company codes are live, there are other statuses present ("Ongoing" or "Scheduled"), plus at least one company code with "Live", the new status of country should be (2) Partially live,
3) If in one country all company codes are either in "Ongoing" or "Scheduled", the new status should be "Ongoing" or "Scheduled" respectively.
I tried to tackle this issue by using SWITCH function in table 2) to assign numeric statuses and then CALCULATE(MAX) in table 1), but the problem is that I cannot capture the logic of showing another status for the country that has same statuses (case 3 with either only "Ongoing" or only "Scheduled").
Any advice will be appreciated.
Thank you.
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" )
11 Replies
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.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.
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?
I want to use the outcome of the column as a legend of the map
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" )
