Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone,
Can someone help me with the DAX-formulas First Keyword and Keywordpath?
FirstKeyword for each customer
I've got the following table and I want a column with the first keyword for each customer, see photo below.
The next challenge is the KeywordPath:
For each customer I want to calculate the KeywordPath, see photo below.
Thanks in advance,
With kind regards,
Cor
The table:
Invoicedate | Invoicenumber | Customernumber | Keyword | Amount excl VAT |
1-1-2018 | 1 | 100 | A | 10,00 |
1-1-2018 | 2 | 200 | B | 20,00 |
1-1-2018 | 3 | 300 | C | 30,00 |
1-1-2018 | 4 | 400 | D | 40,00 |
1-2-2018 | 5 | 100 | 50,00 | |
1-2-2018 | 6 | 200 | 60,00 | |
1-2-2018 | 7 | 300 | 70,00 | |
1-2-2018 | 8 | 400 | 80,00 | |
1-4-2018 | 9 | 100 | K | 90,00 |
1-4-2018 | 10 | 200 | L | 100,00 |
1-4-2018 | 11 | 300 | M | 110,00 |
1-4-2018 | 12 | 400 | N | 120,00 |
1-5-2018 | 13 | 100 | 130,00 | |
1-5-2018 | 14 | 200 | 140,00 | |
1-5-2018 | 15 | 300 | 150,00 | |
1-5-2018 | 16 | 400 | 160,00 | |
1-6-2018 | 17 | 100 | Q | 170,00 |
1-6-2018 | 18 | 200 | R | 180,00 |
1-6-2018 | 19 | 300 | S | 190,00 |
1-6-2018 | 20 | 400 | T | 200,00 |
Solved! Go to Solution.
@Anonymous
For firstKeyWord
Try this Column
First KeyWord = VAR FIRSTNONBLANKKeyWord = TOPN ( 1, FILTER ( Table1, Table1[Customernumber] = EARLIER ( Table1[Customernumber] ) && NOT ( ISBLANK ( Table1[Keyword] ) ) ), [Invoicedate], ASC ) RETURN MINX ( FIRSTNONBLANKKeyWord, [Keyword] )
@Anonymous
For keyword path you can use this MEASURE
Kewwordpath MEASURE = CONCATENATEX ( FILTER ( Table1, Table1[Keyword] <> "" ), Table1[Keyword], ", ", [Keyword] )
@Anonymous
As a calculated column for KewWord Path...you could use
Kewwordpath Column = CONCATENATEX ( FILTER ( Table1, Table1[Customernumber] = EARLIER ( Table1[Customernumber] ) && Table1[Keyword] <> "" ), Table1[Keyword], ", ", [Keyword] )
@Anonymous
For firstKeyWord
Try this Column
First KeyWord = VAR FIRSTNONBLANKKeyWord = TOPN ( 1, FILTER ( Table1, Table1[Customernumber] = EARLIER ( Table1[Customernumber] ) && NOT ( ISBLANK ( Table1[Keyword] ) ) ), [Invoicedate], ASC ) RETURN MINX ( FIRSTNONBLANKKeyWord, [Keyword] )
@Anonymous
For keyword path you can use this MEASURE
Kewwordpath MEASURE = CONCATENATEX ( FILTER ( Table1, Table1[Keyword] <> "" ), Table1[Keyword], ", ", [Keyword] )
You did it again, you've solved both challenges! You've helped me a lot, I've got one final question, is it possible to have the keywordpath as a calculated column? If so, I can calculate what the total amount of each path is.
I'm sorry, I saw you're calculated column-solution late, you can ignore my final question.
You've helped me a lot, you're a real DAX-master, thank you very much!!! 🙂
@Anonymous
Please see attached file
@Anonymous
As a calculated column for KewWord Path...you could use
Kewwordpath Column = CONCATENATEX ( FILTER ( Table1, Table1[Customernumber] = EARLIER ( Table1[Customernumber] ) && Table1[Keyword] <> "" ), Table1[Keyword], ", ", [Keyword] )
User | Count |
---|---|
94 | |
89 | |
79 | |
77 | |
71 |
User | Count |
---|---|
116 | |
107 | |
88 | |
64 | |
63 |