cancel
Showing results for
Did you mean:
Regular Visitor

## Calculate first keyword and calculate a keywordpath

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.

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
3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User

## Re: Calculate first keyword and calculate a keywordpath

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] )```
Super User

## Re: Calculate first keyword and calculate a keywordpath

For keyword path you can use this MEASURE

```Kewwordpath MEASURE =
CONCATENATEX (
FILTER ( Table1, Table1[Keyword] <> "" ),
Table1[Keyword],
", ",
[Keyword]
)```
Super User

## Re: Calculate first keyword and calculate a keywordpath

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]
)```
7 REPLIES 7
Highlighted
Super User

## Re: Calculate first keyword and calculate a keywordpath

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] )```
Regular Visitor

## Re: Calculate first keyword and calculate a keywordpath

Very nice, you've solved the first challenge, great!

Super User

## Re: Calculate first keyword and calculate a keywordpath

For keyword path you can use this MEASURE

```Kewwordpath MEASURE =
CONCATENATEX (
FILTER ( Table1, Table1[Keyword] <> "" ),
Table1[Keyword],
", ",
[Keyword]
)```
Super User

Super User

## Re: Calculate first keyword and calculate a keywordpath

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]
)```
Regular Visitor

## Re: Calculate first keyword and calculate a keywordpath

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.

Regular Visitor