Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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] )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |