Calculate first keyword and calculate a keywordpath

[Anonymous]

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

[Zubair_Muhammad · Accepted 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] )

[Zubair_Muhammad · Accepted solution]

@Anonymous

For keyword path you can use this MEASURE

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

[Zubair_Muhammad · Accepted 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]

@Zubair_Muhammad

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

[Zubair_Muhammad · Accepted solution]

@Anonymous

For keyword path you can use this MEASURE

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

[Anonymous]

@Zubair_Muhammad

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. 

[Anonymous]

@Zubair_Muhammad

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!!! 🙂

[Zubair_Muhammad]

@Anonymous

Please see attached file

[Zubair_Muhammad · Accepted solution]

@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]
)