New column based on DISTINCTCOUNT + IFS?

[Anonymous]

Dear community team,

I'm trying to get the last column in the table below, given that I have a long list of activites, where some things might be repeated.

The feeling is that DISTINCTCOUNT should be somehow involved with maybe CALCULATE + FILTER, but I cannot grasp the idea how exactly I can do that.

Any suggestions how to achieve this using DAX / Query Editor?

Cheers,

Misha

Country	Company	Activity	Cost, USD	In which country the company operates?
USA	A	License purchase	100	Both
USA	B	Power BI training	150	USA
USA	B	Tableau uninstall	0	USA
USA	C	AI / ML training	100	Both
Canada	A	New visuals dev	10	Both
Canada	D	Request processing	5	Canada
Canada	C	Dog walking	100	Both

[AlB · Accepted solution]

Hi @Anonymous 

Try this for a calc column:

NewCol =
VAR Countries_ =
    CALCULATETABLE (
        DISTINCT ( Table1[Country] ),
        ALLEXCEPT ( Table1, Table1[Company] )
    )
RETURN
    SWITCH ( COUNTROWS ( Countries_ ), 1, Table1[Country], 0, BLANK (), "Both" )

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers  

[Anonymous]

Amazing! Many thanks for the quick reply!

Cheers,

Misha