cancel
Showing results for 
Search instead for 
Did you mean: 
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
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors