Forum Discussion

powerbi_bi's avatar
powerbi_bi
Frequent Visitor
9 years ago
Solved

Create a FLAG column with 1 or 0

Hi,

I have a table

IDCASE_IDCOUNTRY
1AAAUSA
1AAAJAPAN
1AAACHINA
2BBBCHINA
2BBBCHINA
2BBBCHINA
2BBBCHINA
3CCCUSA
3CCCJAPAN

 

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

 

IDCASE_IDCOUNTRYCALCULATED_COLUMN
1AAAUSA1
1AAAJAPAN1
1AAACHINA1
2BBBCHINA0
2BBBCHINA0
2BBBCHINA0
2BBBCHINA0
3CCCUSA1
3CCCJAPAN1

 

The condition is:

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

  • Sean's avatar
    Sean
    9 years ago

    powerbi_bi This should work :smileyhappy:

     

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

     

3 Replies

  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    9 years ago

    powerbi_bi This should work :smileyhappy:

     

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

     

    • MarkPalmberg's avatar
      MarkPalmberg
      Icon for Kudo Commander rankKudo Commander
      7 years ago

      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!