cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
corvada Regular Visitor
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.

 

FirstKeyword.PNG

 

The next challenge is the KeywordPath:

For each customer I want to calculate the KeywordPath, see photo below.

 

KeywordPath.PNG

 

Thanks in advance,

 

With kind regards,


Cor

 

The table:

InvoicedateInvoicenumberCustomernumberKeywordAmount excl VAT
1-1-20181100A                        10,00
1-1-20182200B                        20,00
1-1-20183300C                        30,00
1-1-20184400D                        40,00
1-2-20185100                         50,00
1-2-20186200                         60,00
1-2-20187300                         70,00
1-2-20188400                         80,00
1-4-20189100K                        90,00
1-4-201810200L                     100,00
1-4-201811300M                     110,00
1-4-201812400N                     120,00
1-5-201813100                      130,00
1-5-201814200                      140,00
1-5-201815300                      150,00
1-5-201816400                      160,00
1-6-201817100Q                     170,00
1-6-201818200R                     180,00
1-6-201819300S                     190,00
1-6-201820400T                     200,00
3 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Calculate first keyword and calculate a keywordpath

@corvada

 

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
Super User

Re: Calculate first keyword and calculate a keywordpath

@corvada

 

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

@corvada

 

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
Super User

Re: Calculate first keyword and calculate a keywordpath

@corvada

 

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

Re: Calculate first keyword and calculate a keywordpath

@Zubair_Muhammad

 

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

Super User
Super User

Re: Calculate first keyword and calculate a keywordpath

@corvada

 

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

@corvada

 

Please see attached file

Super User
Super User

Re: Calculate first keyword and calculate a keywordpath

@corvada

 

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

Re: Calculate first keyword and calculate a keywordpath

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

Highlighted
corvada Regular Visitor
Regular Visitor

Re: Calculate first keyword and calculate a keywordpath

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