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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
faafreitas
Frequent Visitor

New column to classify based on other column values and filter

Hi, 

 

I'm trying to have a new column that classifies a row based on newest creation date, still filtering by product_type, country, FY_Q.  

 

Expected result:

Created DateRegionCountryFY - QuarterProduct TypeNew_Column
10/27/2022 17:43Asia-PacificASNFY23 - Q1Product A1
11/4/2022 0:23Asia-PacificASNFY23 - Q2Product A1
10/27/2022 17:43Asia-PacificASNFY23 - Q2Product A0
11/4/2022 0:23Asia-PacificASNFY23 - Q3Product A1
10/27/2022 17:43Asia-PacificASNFY23 - Q3Product A0
11/4/2022 0:23Asia-PacificASNFY23 - Q4Product A1
10/27/2022 17:43Asia-PacificASNFY23 - Q4Product A0
11/4/2022 0:23Asia-PacificASNFY23 - Q1Product C1
10/27/2022 17:43Asia-PacificASNFY23 - Q1Product C0
11/4/2022 0:23Asia-PacificASNFY23 - Q2Product C1
10/27/2022 17:43Asia-PacificASNFY23 - Q2Product C0
11/4/2022 0:23Asia-PacificASNFY23 - Q3Product C1
10/27/2022 17:43Asia-PacificASNFY23 - Q3Product C0
11/4/2022 0:23Asia-PacificASNFY23 - Q4Product C1
10/27/2022 17:43Asia-PacificASNFY23 - Q4Product C0
11/4/2022 0:23Asia-PacificASNFY23 - Q1Product D1
10/27/2022 17:43Asia-PacificASNFY23 - Q1Product D0
11/4/2022 0:23Asia-PacificASNFY23 - Q2Product D1
10/27/2022 17:43Asia-PacificASNFY23 - Q2Product D0
11/4/2022 0:23Asia-PacificASNFY23 - Q3Product D1
10/27/2022 17:43Asia-PacificASNFY23 - Q3Product D0
10/27/2022 17:43Asia-PacificASNFY23 - Q4Product D1
11/9/2022 22:14Asia-PacificAUSFY23 - Q1Product A1
10/27/2022 17:43Asia-PacificAUSFY23 - Q1Product A0
11/9/2022 22:14Asia-PacificAUSFY23 - Q2Product A1
10/27/2022 17:43Asia-PacificAUSFY23 - Q2Product A0
11/9/2022 22:14Asia-PacificAUSFY23 - Q3Product A1
10/27/2022 17:43Asia-PacificAUSFY23 - Q3Product A0
11/9/2022 22:14Asia-PacificAUSFY23 - Q4Product A1
10/27/2022 17:43Asia-PacificAUSFY23 - Q4Product A0
11/9/2022 22:14Asia-PacificAUSFY23 - Q1Product B1
11/9/2022 22:14Asia-PacificAUSFY23 - Q2Product B1
11/9/2022 22:14Asia-PacificAUSFY23 - Q3Product B1
11/9/2022 22:14Asia-PacificAUSFY23 - Q4Product B1
11/9/2022 22:14Asia-PacificAUSFY23 - Q1Product C1
10/27/2022 17:43Asia-PacificAUSFY23 - Q1Product C0
11/9/2022 22:14Asia-PacificAUSFY23 - Q2Product C1
10/27/2022 17:43Asia-PacificAUSFY23 - Q2Product C0
11/9/2022 22:14Asia-PacificAUSFY23 - Q3Product C1
10/27/2022 17:43Asia-PacificAUSFY23 - Q3Product C0
11/9/2022 22:14Asia-PacificAUSFY23 - Q4Product C1
10/27/2022 17:43Asia-PacificAUSFY23 - Q4Product C0
11/9/2022 22:14Asia-PacificAUSFY23 - Q1Product D1
10/27/2022 17:43Asia-PacificAUSFY23 - Q1Product D0
11/9/2022 22:14Asia-PacificAUSFY23 - Q2Product D1
10/27/2022 17:43Asia-PacificAUSFY23 - Q2Product D0
11/9/2022 22:14Asia-PacificAUSFY23 - Q3Product D1
10/27/2022 17:43Asia-PacificAUSFY23 - Q3Product D0
11/9/2022 22:14Asia-PacificAUSFY23 - Q4Product D1
10/27/2022 17:43Asia-PacificAUSFY23 - Q4Product D0

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

 

 a new column that classifies a row based on newest creation date, still filtering by product_type, country, FY_Q.  

 

You cannot create columns based on measures/filters. Columns are immutable.

 

You can still add the column but it will have to be based on the full data set.

 

 

Column = 
var c = [Country]
var p = [Product Type]
var f = [FY - Quarter]
var mints = calculate(min('Table'[Created Date]),All('Table'),'Table'[Country]=c,'Table'[Product Type]=p,'Table'[FY - Quarter]=f)
return if([Created Date]=mints,1,0)

see attached

 

View solution in original post

2 REPLIES 2
faafreitas
Frequent Visitor

Hello Ibendlin,

 

Thank you for sharing your solution, it works perfectly and its exactly what I was looking for.

 

Best Regards,

Fernando Freitas

lbendlin
Super User
Super User

 

 a new column that classifies a row based on newest creation date, still filtering by product_type, country, FY_Q.  

 

You cannot create columns based on measures/filters. Columns are immutable.

 

You can still add the column but it will have to be based on the full data set.

 

 

Column = 
var c = [Country]
var p = [Product Type]
var f = [FY - Quarter]
var mints = calculate(min('Table'[Created Date]),All('Table'),'Table'[Country]=c,'Table'[Product Type]=p,'Table'[FY - Quarter]=f)
return if([Created Date]=mints,1,0)

see attached

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.