Forum Discussion

LABrowne's avatar
LABrowne
Helper II
2 years ago
Solved

DAX: Distinct count - only first consecutive string

Hi there, I have an OrderTable,   With Orders[OrderId] and I have multiple orders uploaded, see below example:   123-456 123-456 - Cancelled 123-456 - STW   Now on the system this counts tota...
  • ValtteriN's avatar
    ValtteriN
    2 years ago

    Hi LABrowne ,

    Then this gets a bit tricky. My first suggestion would be to use powerquery if possible. There you could just split via delimiter like this:

    And now calculate the ids from the new column.

    OR:

    With dax you could try something like this:


    Measure 9 =


    COUNTROWS( //count the amount of orders
    SUMMARIZE( //group the table by orderids
            ADDCOLUMNS('Table (12)',"A",
        IF(
           
                SEARCH(" ", 'Table (12)'[Column1], 1, 0)> 0,
            LEFT('Table (12)'[Column1], SEARCH(" ", 'Table (12)'[Column1], 1, 0) - 1),
            'Table (12)'[Column1])),[A]))


    End result:

     



ValtteriN's avatar
ValtteriN
Community Champion
2 years ago

Hi, 

If your order id has a consistent amount of characters here is one way to do this:

Measure 8 =
COUNTROWS( //count the amount of orders
SUMMARIZE( //group the table by orderids
        ADDCOLUMNS('Table (12)',"A",
LEFT('Table (12)'[Column1],7)),[A] //add a column with first 7 characters = actual order id
))


End result:

 

If your orderids have different lenghts ping me with "@" and I will revise the dax.


I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

LABrowne's avatar
LABrowne
Helper II
2 years ago

Hi ValtteriN,

 

Thanks for your help but yes the orderids have different lengths.

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    2 years ago

    Hi LABrowne ,

    Then this gets a bit tricky. My first suggestion would be to use powerquery if possible. There you could just split via delimiter like this:

    And now calculate the ids from the new column.

    OR:

    With dax you could try something like this:


    Measure 9 =


    COUNTROWS( //count the amount of orders
    SUMMARIZE( //group the table by orderids
            ADDCOLUMNS('Table (12)',"A",
        IF(
           
                SEARCH(" ", 'Table (12)'[Column1], 1, 0)> 0,
            LEFT('Table (12)'[Column1], SEARCH(" ", 'Table (12)'[Column1], 1, 0) - 1),
            'Table (12)'[Column1])),[A]))


    End result: