Create custom column using max and filter

[Milan6687]

Hi,

I have a table like below:

Id     Name   Type Date

100   Apple    fruit   12/01/2020

100   Apple     sweet 15/01/2020

I want to create the report where Type =fruit and one date column new_date=max(Date) where Type=sweet

Id Name Type New_Date

100 Apple fruit  15/01/2020

I tried many options but failed to to get desird result.

Thanks,

Milan

[Anonymous · Accepted solution]

Create new table,

Table 2 = SUMMARIZE('Table','Table'[ID],'Table'[Name],'Table'[Type],"New_date",CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[ID]),'Table'[Type]="Sweet"))

Or if you want to do by previous method then

create measure as below

Measure = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[ID]),'Table'[Type]="Sweet")

and filtet out type.

www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Milan6687]

Hi,

Actually i gave you incomplete data. My apology.

Data looks like:

ID Name Type Date

100 apple  fruit 01/01/2020

100 apple  sweet 02/02/2020

200  banana fruit 03/03/2020

200 banana sweet 04/04/2020

Expected output

ID Name new_Date

100 apple 02/02/2020

200 banana 04/04/2020

filter: Type=fruit

New_date logic= Max(date) where type=sweet

I tried:

CALCULATE(MAX(Table(date),FILTER(Table,table[type]="sweet"))

but it gave me

ID Name new_Date

100 apple 04/04/2020

200 banana 04/04/2020

I think I need to put GROUPBY but exactly how and where its where i am struggling.

Thanks,

Milan

[amitchandak]

You are taking the max of the date on fruit, then why type filter.

Min of type and max of date will work as long as view/group by is fruit.

[Anonymous · Accepted solution]

Create new table,

Table 2 = SUMMARIZE('Table','Table'[ID],'Table'[Name],'Table'[Type],"New_date",CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[ID]),'Table'[Type]="Sweet"))

Or if you want to do by previous method then

create measure as below

Measure = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[ID]),'Table'[Type]="Sweet")

and filtet out type.

www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Milan6687]

Thanku 2nd option worked for me.

Thanks,

Milan

[Anonymous]

New table=Summerize(filter(table,table[type]="Sweet"),table[id],Table[Name],Table[Type],"New_Date",MAx(Table[Date]))

Thanks,

Pravin

www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Milan6687]

Hi,

Actually I don't want to create new table. Can it be done using create new column on the same table.

Appriciate your help.

Thanks,

Milan

[Anonymous · Accepted solution]

New Column = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[ID]),'Table'[Type]="Sweet")

 and filter out type.

Thanks,

Pravin

If I resolve your problem Mark it as a solution and give kudos.

[amitchandak]

Take Min of Type and Max of Fruit in Visual or create a new table like

new table = summarize(table,table[Id],table[Name],"_Type",Min(Type),"_date",Max(Date))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Datanaut Connect on Linkedin

[Anonymous]

Create one measure

Measure= Calculate(Max(table[Date],all(table)

Then drag id,name,type,measure in table visual and go to visual level filter and set type is fruit.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.