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)
Helper IHello,
I have found a very interesting tutorial to do what I intended to do.
This is more merging columns than creating a new conditionnal column.
Tutorial here : https://powerpivotpro.com/2019/02/powerquerymagic-conditional-joins-using-table-selectrows/
Thank you so much Justin Mannhardt for putting up such a great tutorial !
Have a great day !
- Greg_Deckler
Community Champion
blackbool44 - 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],", " )- blackbool44
thank 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_DecklerSorry blackbool44 yesterday was a crazy busy day. Glad you found a solution. I agree, that's a much better approach than trying to shoe horn this into a DAX calculated column.
It's an interesting problem though so I may still take a look at this when I have more time because, you never know. Often, you can learn a lot by trying to do the impossible/ridiculous.