DAX: Distinct count - only first consecutive string

[LABrowne]

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!

[AnalyticPulse]

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

[ValtteriN]

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]

Hi @ValtteriN,

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

[ValtteriN · Accepted solution]

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: