Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DAX Joining 2 tables in SWITCH statement

 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])

Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    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

4 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Icon for Super User rankSuper User
    5 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

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

  • PhilipTreacy's avatar
    PhilipTreacy
    Icon for Super User rankSuper User
    5 years ago

    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