MAX with condition

[philosophie_e]

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.

[smpa01]

@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]

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

[smpa01]

@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]

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]

@philosophie_e  provide t1 and t2 data only for JAPAN

[philosophie_e]

Okay, I think I know what is the issue.... Japan has two company codes that have statuses "Scheduled" and "Ongoing"... Meaning that the final outcome cannot be one out of the final list I had provided. It should be "Not live". I need to add one more entry in my calculated column to get those cases showing. Correct?

[smpa01 · Accepted solution]

@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"
    )

[philosophie_e]

Hello!

The code is working perfectly well. Now I am trying to introduce some changes to the formula, namely in this place:

        _live <> BLANK ()
            && _live = _status, "Fully Live",
        _live <> BLANK ()
            && _live <> _status, "Partially Live",

Now I have two new statuses that should also resemble "Live". 
Meaning that if there is a combination of status "Live" and one of those two statuses, I should get "Fully live" (meaning that those statuses presuppose live status). 
If there is combination of new statuses with "Ongoing"/"Scheduled", then the end result should be "Ongoing"/"Scheduled".  I additionally created two new variables for this purpose: new1 and new2.

I tried to add it as (1):
_live <> blank ()
        && (_live || _new1 || _new2) = _status, "Fully live",

but then all values "Partially live" are getting overwritten with other values...

I also tried adding conditions separately with but it also did not work (2):
_new1<> blank ()
        && _new1 = _status, "Fully live", 

but it didn't work due to "<>".

        _live <> BLANK ()
            && _live <> _status, "Partially Live",

How can I adjust the current formula to add two new statuses that will present "live" status.

Thank you!

[philosophie_e]

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?
 

[sevenhills]

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]

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.