Create a FLAG column with 1 or 0

[powerbi_bi]

Hi,

I have a table

ID	CASE_ID	COUNTRY
1	AAA	USA
1	AAA	JAPAN
1	AAA	CHINA
2	BBB	CHINA
2	BBB	CHINA
2	BBB	CHINA
2	BBB	CHINA
3	CCC	USA
3	CCC	JAPAN

I want to create a FLAG (Calculated_Column) with 1 or 0 based on the conditions

ID	CASE_ID	COUNTRY	CALCULATED_COLUMN
1	AAA	USA	1
1	AAA	JAPAN	1
1	AAA	CHINA	1
2	BBB	CHINA	0
2	BBB	CHINA	0
2	BBB	CHINA	0
2	BBB	CHINA	0
3	CCC	USA	1
3	CCC	JAPAN	1

The condition is:

If one CASE_ID has more than one country assigned then its 1 else 0

[Sean · Accepted solution]

@powerbi_bi This should work

Flag Column =
IF (
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[COUNTRY] ),
        ALLEXCEPT ( 'Table', 'Table'[CASE_ID] )
    )
        = 1,
    0,
    1
)

[MarkPalmberg]

I have a similar question based on the same data. How does the calculation change if I want my flag column to indicate whether a case has a CHINA value? Do I need a filter value in the DISTINCTCOUNT function?

My use case is that I want to see all countries for cases that have a CHINA value. Thanks!

[powerbi_bi]

Thanks Sean for the quick reply
Works Great!