Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
tsoulge
Helper I
Helper I

How to get the unique customer name within selected period range based on min Date

Hi all,

 

I would like to have, in a table, the customer ID, the customer name and the order date like the one below:

 

Customer IDCustomer NameOrder Date
1Cust A01/01/2023
2Cust B01/02/2023
3Cust C01/03/2023

 

My challenge is that the user will have the possibility to define a start date and an end date via a slicer and we have multiple order dates per period. So, let's take the scenario that the user selects the period from Jan 23' - Mar 23, the table will show something like that:

 

Customer IDCustomer NameOrder Date
1Cust A01/01/2023
2Cust B01/02/2023
2Cust B15/02/2023
3Cust C01/03/2023
3Cust C15/03/2023

 

Any idea how can I show only the unique Customers, based on the 1st Order Date that happened per ID (like min(Order Date)), so the table will look like as the 1st table (above)?

 

Many thanks!

4 REPLIES 4
tsoulge
Helper I
Helper I

Hi @v-yangliu-msft,

 

Please let me know if you have any further ideas about my question.


FYI, I need also to take into consideration the date slicer selection in my flag's DAX formula (which I use the column [Date] from 'Date' table. So, I need to add in the existing DAX the logic:

'Table'[Order Date] >= minx(allselected(date),'date'[date])

 

Many thanks!

v-yangliu-msft
Community Support
Community Support

Hi  @tsoulge ,

 

Here are the steps you can follow:

1. Create measure.

Flag =
IF(
    MAX('Table'[Order Date]) = MINX(FILTER(ALL('Table'),'Table'[Customer ID]=MAX('Table'[Customer ID])&&'Table'[Customer Name]=MAX('Table'[Customer Name])),[Order Date]),1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

vyangliumsft_0-1685413617892.png

3. Result:

vyangliumsft_1-1685413617893.png

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Hi @v-yangliu-msft 

 

Thanks for your response. The solution works except one important case:

 

If we have the same ID but a different Customer Name (which is normal since the selection period reflects many years), the table shows both rows. My expectation is to have a table with unique IDs, without taking account any differences in the Customer Names. Any solution about this issue?

 

Thanks again!

tsoulge
Helper I
Helper I

Hello everyone,

 

Any update you may have would be greatly appreciated, since I'm working on it to a very strict deadline.

 

Also, in case it helps, I have tried to use LASTNONBLANK or LASTNONBLANKVALUE functions with no success so far, so please let me know if you have a better solution.

 

Thanks again.

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.