Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

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

tabs3.png

 

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

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

4 REPLIES 4
Anonymous
Not applicable

@PhilipTreacy  tat worked . thank you so much. 

PhilipTreacy
Super User
Super User

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

tabs3.png

 

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

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Anonymous
Not applicable

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

PhilipTreacy
Super User
Super User

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.