DAX Joining 2 tables in SWITCH statement

[Anonymous]

 Hi Below is my SWITCH in DAX which is working perfectly . Now I want to add a new condition by joining on another table. the join relation already exists in the model.

basically join on table called Development and we need to add another condition to the below which says WHERE Development.FLAG = 'FALSE'. Also i need to add another VALUE in this switch called 'NHT' and the condition for this is WHERE Development.FLAG = 'TRUE' THEN 'NHT'

can someone please update my below SWITCH to my new condition's please ? Many Thanks 

record_type_report = SWITCH( TRUE(),[Recordtypename__c] = "Places Development OS","Open Market",
               [Recordtypename__c] = "Places Development SO","Shared Ownership",
               [Recordtypename__c] = "Places Development SE","Shared Equity",
               [Recordtypename__c])

[Anonymous]

@PhilipTreacy  tat worked . thank you so much. 

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

Please @ mention me in replies or I'll miss your response.  Type @ then select my name.

Download sample PBIX with the following code and data

No it won't but I've amended the code to this which does work.  RELATED needs a row context to work so I'm using LOOKUPVALUE on the related Development table.

record_type_report = 

VAR __Flag = LOOKUPVALUE('Development'[Flag], 'Development'[Value], SELECTEDVALUE('Table'[Value]))

RETURN

SWITCH( 
    
    TRUE(), 
        
    [Recordtypename__c] = "Places Development OS" && __Flag = FALSE ,"Open Market",

    [Recordtypename__c] = "Places Development SO" && __Flag = FALSE ,"Shared Ownership",

    [Recordtypename__c] = "Places Development SE" && __Flag = FALSE ,"Shared Equity",

    __Flag = TRUE, "NHT",

    [Recordtypename__c] 

)

Which gives this

Is that the result you were looking for when 'Development'[Flag] = True to give NHT ?

Regards

Phil

[Anonymous]

Hi Phil, but will that work without using the RELATED function as you are joining with another table ? thanks

[PhilipTreacy]

Hi @Anonymous 

Try this 

record_type_report = SWITCH( 
    
    TRUE(),
    
    [Recordtypename__c] = "Places Development OS" && 'Development'[Flag] = FALSE ,"Open Market",

    [Recordtypename__c] = "Places Development SO" && 'Development'[Flag] = FALSE ,"Shared Ownership",

    [Recordtypename__c] = "Places Development SE" && 'Development'[Flag] = FALSE ,"Shared Equity",

    'Development'[Flag] = TRUE, "NHT"

    [Recordtypename__c]
)

There are some assumptions here:

1. Flag is a column in the Development table.

2. This Flad column is Boolean True/False.  If it contains text then you need to change the TRUE/FALSE values in the code above to strings e.g. "True" and "False"

Regards

Phil