Forum Discussion
Using values from two tables to create a calculated DAX column with Switch()/IN() statement
Good Afternoon,
I am new PBI community member but I have been working with Power BI for a couple years at work. I have finally run into something that I could not find an answer to via searching the forums. My problem is as follows:
I have a large number of computer devices throughout my organization, ~15,000. The names of these machines reflect what region they are in (we have 147 regions). What I am attempting to do is create a column that looks at the machine name and uses something like a switch statement to give a result of the region code. The challenge is that sometimes, due to some previous naming conventions, the machine name may contain a numeric code instead of the alpha code for the region, so I am trying to see if I can basically store the values from a column in one table and use those inside of a formula to calculate the new column.
Here are some examples of what I have and what I am trying to get. In Table1, the "Region" is what I am trying to get with the forumula.
Table1
Table1
|
Table2
|
What I am trying to do with the formula is look at ComputerName in Table1 and if it finds a letter sequence that matches a RegionCode in Table2, it puts that code in Region Column, otherwise if a numerical sequence matches RegionNumber, use that to get the value of RegionCode and add that to the column. I am not against using multiple columns if needed, but my current method would use a Switch statement with over 500 cases, and I was hoping there was a more efficient way of doing it. Here is the formula I have so far, thanks to v-yuta-msft in a previous post.
Region =
SWITCH(
TRUE(),
ISERROR( FIND( "ABCD", 'Table1'[ComputerName])) <> TRUE(), "ABCD",
ISERROR( FIND( "1234", 'Table1'[ComputerName])) <> TRUE(), "ABCD",
ISERROR( FIND( "DEFG", 'Table1'[ComputerName])) <> TRUE(), "DEFG",
ISERROR( FIND( "4567", 'Table1'[ComputerName])) <> TRUE(), "DEFG",
ISERROR( FIND( "GHIK", 'Table1'[ComputerName])) <> TRUE(), "GHIK",
ISERROR( FIND( "26381", 'Table1'[ComputerName])) <> TRUE(), "GHIK",
...
As you can see, doing this for every value I have in Table2 would create a very long SWITCH() formula. I was hoping to find something like I have done in PowerShell where I can store or read the values in Table2 and use an IN ('Table1'[ComputerName]) or something similar.
Any help is very much appreciated!
3 Replies
Hi Semipro211
Please see the attached file with a solution in Power Query
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedInHi Mariusz
Thank you for taking the time to respond. I am surprised how simple the M code is to do this! I did run into one issue though. When I attempt to duplicate this code in my own pbix project, my expanded custom column gives me null values. Due to the sensitive nature of the actual information I am working with, I cannot post my pbix file, but I can at least give the structure of the two real tables with their actual column names and some dummy data. All columns are text columns.
AD Complete
CN TWWSOV00985231N LWYKTC21443004L EXANNZ20187333M NCOM20071133W CLC_CSI
CLC CSI 985 WSOV 21443 YKTC 20187 ANNZ 20071 ANNZ let (ommitted for brevity) #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows( CLC_CSI, (b) => Text.Contains( [CN], b[CLC] ) ) ), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"CSI"}, {"CSI"}) in #"Expanded Custom"I don't get any errors, but every value is null.
Mariusz Allow me to correct myself, I must have misinterpretted part of the M code in your solution, when I changed b[CLC] to b[CSI], the column now works as expected/hoped.
THANK YOU!
