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

 

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

View solution in original post

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

View solution in original post

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

View solution in original post

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

View solution in original post

corvada Member
Member

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

View solution in original post

Super User
Super User

Re: Calculate first keyword and calculate a keywordpath

@corvada

 

Please see attached file

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

View solution in original post

corvada Member
Member

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. 

corvada Member
Member

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 6 members 941 guests
Please welcome our newest community members: