Dax Logic Ranking

[lherbert501]

Hi,

I'm really stuck with some logic in dax. Could somebody please help? 

I need country 1 for an item to take priority unless country 1 is 1 and country 2 is not.

I have two rows for each item one for each of the two countries. I need it to say

If the items status in country 1 is 1 and country 2 is not 1 then take country 2's status for that item on both lines

else take the status on country 1 for both lines.

This happens on item 2 below as example.

Thanks

[TomMartens · Accepted solution]

Hey @lherbert501 ,

I use DAX to create a calculated column, this is the DAX statement:

Column = 
var statusC1 = CALCULATE( MAX( 'Table'[Status] ) , ALLEXCEPT( 'Table' , 'Table'[item] ) , 'Table'[Country] = "Country 1" )
var statusC2 = CALCULATE( MAX( 'Table'[Status] ) , ALLEXCEPT( 'Table' , 'Table'[item] ) , 'Table'[Country] = "Country 2" )
return
IF( statusC1 = 1 && statusC2 <> 1 , statusC2 , statusC1 )

and this is how the table looks like:

Hopefully, this provides what you are looking for.

Regards,

Tom

[lherbert501]

Thankyou so much @TomMartens