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 total orders as 3, which is correct there is 3 orders on the system.

 

Is there any way to write a dax formula that ignore any characters (inc. space) and only count the first consecutive string? So that the above order count would be 1 if that makes sense.

 

Any help would be great!

  • 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:

     



4 Replies

  • 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/

      • 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:

         



  • AnalyticPulse's avatar
    AnalyticPulse
    Solution Sage
    2 years ago

    very simple.

    add below column in your table and use that column in your visual:

    NewColumn = SUBSTITUTE(SUBSTITUTE(Orders[OrderID], " - cancelled", ""), " - STV", "")

     

    this will replace the ext "- cancelled" and "- STV" with an empty string, effectively removing these parts from the OrderId column. The result is stored in the new column called NewColumn

    If this helped, Subscribe AnalyticPulse on YouTube for future updates:
    https://www.youtube.com/@AnalyticPulse
    https://instagram.com/analytic_pulse
    https://analyticpulse.blogspot.com/

    subscribe to Youtube channel For fun facts:
    https://www.youtube.com/@CogniJourney