topic DAX: Distinct count - only first consecutive string in DAX Commands and Tips

DAX: Distinct count - only first consecutive string

LABrowne — Tue, 21 Nov 2023 10:06:32 GMT

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!

Re: DAX: Distinct count - only first consecutive string

ValtteriN — Tue, 21 Nov 2023 10:35:38 GMT

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/

Re: DAX: Distinct count - only first consecutive string

AnalyticPulse — Tue, 21 Nov 2023 10:38:41 GMT

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

Re: DAX: Distinct count - only first consecutive string

LABrowne — Tue, 21 Nov 2023 11:09:50 GMT

Hi ValtteriN,

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

Re: DAX: Distinct count - only first consecutive string

ValtteriN — Fri, 24 Nov 2023 05:40:07 GMT

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: