Case statement comparing two columns - DAX FUNCTION

[revansh]

Hi All,

        Could you provide some inputs in writing a dax function for below scenario.

Case

when (table1[column1] = 1 and table2[column1] = 1) then 1

when (table1[column1] = 1 and table2[column1] = 0) then 0

when (table1[column1] = 0 and table2[column1] = 1) then 0

when (table1[column1] = 0 and table2[column1] = 0) then 1

end

there is join between TABLE1 and TABLE2 defined in datamodel.

Thanks

[v-shex-msft · Accepted solution]

Hi @revansh,

If you share some detail contents, it will be help to clarify your scenario.

If you already cross join or use related function to merge these columns to one table, you can try to use below formula to create a calculate column to return the result.

Result=if([T1 Column1]-[T2 Column1]=0,1,0)

Regards,

Xiaoxin Sheng

[Daniil]

I would simplify the case statement for DAX purposes. Your case statement returns 1 if either both columns are 1 or both are 0; otherwise, it returns 0.

Two questions:

1. Are you trying to create a calculated column or a measure?
2. If you are trying to create a calculated column, which table is on the many side of the relationship?

Three possibilities:

1. If you are trying to create a calculated column and table1 is on the many side, try this:
   

   YourColumnName = INT ( table1[column1] = RELATED ( table2[column1] ) )

2. If you are trying to create a calculated column and table2 is on the many side, try this:
   

   YourColumnName = INT ( table2[column1] = RELATED ( table1[column1] ) )

3. If you are trying to create a measure, try this:
   

   YourMeasureName = INT ( SELECTEDVALUE ( table1[column1] ) = SELECTEDVALUE ( table2[column1] ) )