cancel
Showing results for
Did you mean:
Highlighted
Helper I

## Count unique values in a filtered column with earlier

Hey everybody,

I don´t know why, but the forum decided to delete my post, so I am just typing it again...

I am stuck with this problem since a while now, have googled and read at least 50 posts, but have not found help so far. I hope, somebody has an idea here.

The question I want to answer is quite simple. How many shipments are needed to fulfil one order.
The dataset I am working with contains information about orders. If you order different product at the same time, for each of these product there will be a record - an order line. But they will get the same order id. Now due to stock levels and other reasons not all order lines of one order are shipped at the same time. Each order lines has an shipping id, which shows in which shipment the order was sent.

So in a simplified version:

 order_id shipping_id 1 a 1 b 1 c 2 a 2 b 3 e 3 e 3 e 3 e

The outcome should look like:

 order id shipping id shipping ids per order id 1 a 3 1 b 3 1 c 3 2 a 2 2 b 2 3 e 1 3 e 1 3 e 1

First, I tried to generate a table for each row which only shows the data with the exact same order id. To check, if my formula is correct, I counted the rows and controlled it manually. That worked:

=COUNTX(FILTER('Outbound_3_ESC',EARLIER('Outbound_3_ESC'[order_id]) = 'Outbound_3_ESC'[order_id]),'Outbound_3_ESC'[order_id])

Then, I tried to count the distinct values for the shipping id in those tables:

Shipments per order_id = CALCULATE(DISTINCTCOUNT(Outbound_3_ESC[shipment_id]),FILTER('Outbound_3_ESC',EARLIER(Outbound_3_ESC[order_id]) = 'Outbound_3_ESC'[order_id]))

The formula works, however the outcomes are wrong. I don´t know what is counted there.... I read in several posts, that this could happen, because I am doing it in a calculated column. And the solution in the forum always was to use a measure. However, in a measure the earlier function is not working... And there I am stucked.

Comments, feedback and solutions are well appreciated. I also am happy to answer any questions, which might appear regarding my problem description.

Thanks in advance for any help!
Jakob
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft

## Re: Count unique values in a filtered column with earlier

Hi @staryuz ,

Please try to create a calculated column as below to replace that measure and check if that is what you want:

``````Column = CALCULATE (
DISTINCTCOUNT ( 'Outbound_3_ESC'[shipment_id] ),
FILTER (
ALLSELECTED(    'Outbound_3_ESC' ),
'Outbound_3_ESC'[order_id] = EARLIER( 'Outbound_3_ESC'[order_id] )
)
)``````

Best Regards

Rena

12 REPLIES 12
Highlighted
Super User V

## Re: Count unique values in a filtered column with earlier

Hi @staryuz ,

Have you tried this:

Shipping id per order id = CALCULATE(DISTINCTCOUNT('Table'[shipping_id]), ALLEXCEPT('Table','Table'[order_id ]))

Proud to be a Super User!

Highlighted
Helper I

## Re: Count unique values in a filtered column with earlier

Hey camargos88,

The outcome is the same than with my approach - the calculated column shows wrong numbers.
For example there is an order with 130 order lines, but only 4 shipment ids are used in these order lines. However, yours and my formula show 59.

Highlighted
Super User IX

## Re: Count unique values in a filtered column with earlier

@staryuz , Try as new column

calculate(distinctcount(Table[shipment id]), filter(Table, [order id] = earlier([order id])))

Proud to be a Super User!

Highlighted
Helper I

Hey

Highlighted
Microsoft

## Re: Count unique values in a filtered column with earlier

Hi @staryuz ，

Please try to create measures as below to replace the original calculated column:

``````Measure =
CALCULATE (
DISTINCTCOUNT ( 'Outbound_3_ESC'[shipment_id] ),
FILTER (
ALLSELECTED ( 'Outbound_3_ESC' ),
'Outbound_3_ESC'[order_id] = MAX ( 'Outbound_3_ESC'[order_id] )
)
)``````
``Measure 2 = SUMX(VALUES('Outbound_3_ESC'[order_id]),[Measure])``

Best Regards

Rena

Highlighted
Helper I

## Re: Count unique values in a filtered column with earlier

Hey Rena,

thanks for your help. That worked!

I want to show the average of the ratio. How could I bring that into a visual?
Do I understand your formula right - Can I simply change the SUMX into AVERAGEX to get the averages into the visual?

``AVERAGEX(VALUES('Outbound_3_ESC'[order_id]),[Measure])``

Highlighted
Microsoft

## Re: Count unique values in a filtered column with earlier

Hi @staryuz ,

Whether the following table is what you want? If no, please provide more details and examples about your expected result.

Best Regards

Rena

Highlighted
Helper I

## Re: Count unique values in a filtered column with earlier

Hey Rena,

The idea is to show the average number of shipments per order id in a column chart. Something like this:

If I enter the measure as value for the column chart it shows the sum of all measures separated into years. So I get the total number and not the average in the visualization.

Thanks and best regards
Jakob

Highlighted
Microsoft

## Re: Count unique values in a filtered column with earlier

Hi @staryuz ,

Sorry that I still not clear about your requirement. Could you please provide the correct average number of shipments per order id in the form of table just like the one in your original post? As checked your column chart, the date field be displayed as Axis fields. So where this date field is from? Whether it is from the same table with order id field?

The outcome should look like:

 order id shipping id shipping ids per order id 1 a 3 1 b 3 1 c 3 2 a 2 2 b 2 3 e 1 3 e 1 3 e 1

Best Regards

Rena

Announcements

#### Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors