cancel
Showing results for
Did you mean:
Member

## 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
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]
)```
Highlighted
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
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] )```
Member

## 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

Highlighted
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]
)```
Member

## 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.

Member

## Re: Calculate first keyword and calculate a keywordpath

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!!! :-)

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 6 members 941 guests
Recent signins: