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:

     



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