Forum Discussion
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...
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 ordersSUMMARIZE( //group the table by orderidsADDCOLUMNS('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:
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:
End result: