Forum Discussion
Helper Iaccount statement : if a text string contains keywords inside a table then input column title
Ah, the double underscores. Well, two reasons.
1. I think it is important to keep things that I create as variables obvious to myself. It is very easy in complex DAX calculations to start confusing yourself as to what is a column, a measure, a variable, etc. Just ensures that I don't name a variable a reserved word, etc. It's just a convention I came up with that I probably picked up from someone else or who knows. It's sort of the same reason I tend to be a stickler for capatilizing DAX functions, we all have our habits.
2. The double underscore. It's a rhythm thing. For some reason I find it more in rhythm with my typing to do a double underscore versus a single underscore. So Shift __ is just more rhythmatic for me than Shift _, it's like I find myself pausing for a second after just doing a single underscore and then trying to type a letter but with double underscore I don't have that pause. I don't know, maybe I'm weird like that.
Let me see:
Transaction Category = VAR __Table = ADDCOLUMNS( 'Table', "Search",FIND([Value],[Transaction Description],,-1) ) VAR __Category = CONCATENATEX(DISTINCT(SELECTCOLUMNS(FILTER(__Table,[Search]<>-1),"Attribute",[Attribute])),[Attribute],", ") RETURN IF(LEN(__Category)=0,"Others",__Category)
Community Championblackbool44 - You know, this didn't turn out to be as nearly as ugly as I had originally thought once I unpivoted your first table and cleaned up some of the data (get rid of blanks, fix " .COM" to ".COM" for example. Once that was done, the following works (below). I attached the PBIX for reference as well. Man, the CONCATENATEX function has been coming in handy lately!
Transaction Category =
VAR __Table =
ADDCOLUMNS(
'Table',
"Search",FIND([Value],[Transaction Description],,-1)
)
RETURN
CONCATENATEX(
DISTINCT(
SELECTCOLUMNS(
FILTER(__Table,[Search]<>-1)
,"Attribute",[Attribute]
)
),
[Attribute],", "
)
Helper Ithank you so much Greg_Deckler .
What a nice solution !
I see that you have used a variable DAX Keyword....I will definitely have to learn how to use it.
I am sure a lot of people who are in the same situation will find your code very useful !!
take care !
Thomas
- blackbool44
Hi again Greg_Deckler ,
Just a quick question :
in your code, what is the reason you put __ (double underscore) in front of the VAR__Table ?
I am trying to include a formula at the end of your code to replace the Blank values by "Others".
I was thinking about something like this :Transaction Category = VAR __Table = ADDCOLUMNS( 'Table', "Search",FIND([Value],[Transaction Description],,-1) ) RETURN CONCATENATEX(DISTINCT(SELECTCOLUMNS(FILTER(__Table,[Search]<>-1),"Attribute",[Attribute])),[Attribute],", ") IF(Len(__Table)=0,"Others",__Table)How would you make it work ?
Thanks a lot
Thomas- Greg_Deckler
Ah, the double underscores. Well, two reasons.
1. I think it is important to keep things that I create as variables obvious to myself. It is very easy in complex DAX calculations to start confusing yourself as to what is a column, a measure, a variable, etc. Just ensures that I don't name a variable a reserved word, etc. It's just a convention I came up with that I probably picked up from someone else or who knows. It's sort of the same reason I tend to be a stickler for capatilizing DAX functions, we all have our habits.
2. The double underscore. It's a rhythm thing. For some reason I find it more in rhythm with my typing to do a double underscore versus a single underscore. So Shift __ is just more rhythmatic for me than Shift _, it's like I find myself pausing for a second after just doing a single underscore and then trying to type a letter but with double underscore I don't have that pause. I don't know, maybe I'm weird like that.
Let me see:
Transaction Category = VAR __Table = ADDCOLUMNS( 'Table', "Search",FIND([Value],[Transaction Description],,-1) ) VAR __Category = CONCATENATEX(DISTINCT(SELECTCOLUMNS(FILTER(__Table,[Search]<>-1),"Attribute",[Attribute])),[Attribute],", ") RETURN IF(LEN(__Category)=0,"Others",__Category)- blackbool44
Thank you so much Greg_Deckler for taking the time to share your best practice and solution !
You are really helping me to progress big time in DAX.
Cheers !
Thomas