cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
bo_afk Member
Member

Calculated table with filters

I have a dataset that displays customers' orders each day, with a column to also determine the customer's orderSequenceNumber i.e. on their first order this is 1, on their next order this is 2 and so forth.

 

Sample dataset 

YearMonthDayCustomerIDCustomerOrderSeqNo
2019September14abc1231
2019September16abc1232
2019September20abc1233
2019September21abc1234
2019September26abc1235

 

Using this sequenceNo, I will define whether the customer is new or existing. On 14th September, the customer is considered 'new' (denoted by the number 1) but on 16th Septemer the customer is now 'existing'.

 

However, if in my filter I were to select multiple dates (e.g. 14th, 16th and 20th), I would like to see whether the customer was either new or existing within this selected period. In this case, they are considered 'new' since their minimum sequenceNo is 1.

 

If filter was just 16th and 20th, they will be considered 'existing' since their minimum sequence number is greater than 1.

 

I would like to then create a filter for new and existing customers which would change dependant on date filter.

 

Does anyone know if this is possible?

 

Many thanks!

afk

3 REPLIES 3
AWA Regular Visitor
Regular Visitor

Re: Calculated table with filters

Hi

I'm not sure if i've understood your problem fully, but try the following.

Create a new column with the following calculation: 

IF CustoemrOrderSeqNo = 1 THEN "New" ELSE "Existing" ENDIF

Now you can make a single-select slicer and choose whether you want to see new or existing customers in your list. So first choose date interval, then choose if you want to see new or existing customers in your visualization.

Let me know if this helps.

bo_afk Member
Member

Re: Calculated table with filters

Hi @AWA 

 

Thanks for your suggestion.

Unfortuantely this doesnt work in my case since the status of the customer changes according to the filter selection of the date. The column only provides a static view of the customer's status at just one point in time whereas mine needs to be dynamic.

 

afk

Re: Calculated table with filters

Hi  @bo_afk ,

For a dynamic view of the customer's status, I think you can create a measure like this:

Measure 2 = RANKX(FILTER(ALLSELECTED('Test'),Test[CustomerID]=MAX(Test[CustomerID])),CALCULATE(SUM('Test'[date])),,ASC)

The results are as follows:

measure 1.PNG

 

Here is a demo , please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EfcmsZK-GDBDqpDK5p...

 

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 296 members 3,638 guests
Please welcome our newest community members: