Forum Discussion
VAR and SWITCH in Create New Column error
Hello,
I'm looking to create my first "new column" using power bi desktop. I may be off base and using DAX instead of M. In essence I'm taking the last 4 digits of a provider number and creating a Text Category depending on this value. It is choking at the Temp Variable I set up which it highlights in Blue in the photo below. For the full statement I used...read below the photo. So any general steering you folks can provide would be appreciated. Tom
Here's the expression I'm using:
=
VAR
Temp = [PROVIDER_NUMBER]-((TRUNC ([PROVIDER_NUMBER]/10000))*10000)
RETURN
SWITCH (
TRUE(),
Temp >= 1 && Temp <= 879, "GenAcute",
Temp >= 1300 && Temp <= 1399, "Rural PCH",
Temp >= 3300 && Temp <= 3399, "Childrens",
Temp >= 3400 && Temp <= 3499, "Rural PCH",
Temp >= 3975 && Temp <= 3999, "Rural PCH",
Temp >= 8500 && Temp <= 8899, "Rural PCH",
Temp >= 3300 && Temp <= 3399, "Rural PCH",
"Other")
6 Replies
You did everything exactly right IF you were creating a calculated column in DAX BUT as you mentioned you wrote this in the query editor which uses the M language. Copy that formula, load the query and then go to the table and add a calculated column with that exact same logic.
Alright!! At least the DAX looks right. I'll take a look at M and see what might do the trick there since I would then never have to think about it again on data refresh. I'm guessing an intermediate col would be useful and then not load it....to break it into pieces.
Thanks again, and I'll try out the code above just for the satisfaction of it and then crack the M book. Tom
I really should ask: "do you have any tips for the approach in M?" Is it to create a temp column and build a big nested IF (presuming there is such a thing in M)? Thanks for any help in advance. Tom
