Calculate maximum of 2 columns in 1 column

[ElenaGrom]

Hello Community,

My goal is to calculate the maximum value via a calculated column, filtered by row values from another column.

I have a FACT table linked as n:1 to ProductCategory table via ProductCategory field.

I need to add "MaxCategory" field to FACT table, which should display the maximum CategoryID from ProductCategory table for each ProductCode.

So far I can make it by adding 2 columns to FACT, and the achieved result is OK:

CurrentCategory = 

LOOKUPVALUE(ProductCategory[CategoryID],ProductCategory[ProductCategory],'FACT'[ProductCategory])

MaxCategory1 =

calculate(max('FACT'[CurrentCategory]),ALLEXCEPT('FACT','FACT'[ProductCode]))

Question: is there a way to get to the same result, but having only 1 column added instead of 2?

I can think of the following code with 2 options, but both bring the wrong result.

MaxCategory2 =

Var CurrentCategory =

LOOKUPVALUE(ProductCategory[CategoryID],ProductCategory[ProductCategory],'FACT'[ProductCategory])

//Option1

Var MaxCategory = maxx(filter('FACT','FACT'[ProductCode]),CurrentCategory)

//Option2

VAR MaxCategory = maxx(ALLEXCEPT('FACT','FACT'[ProductCode]),CurrentCategory)

Return

MaxCategory

Your help will be much appreciated!

Here is the PBIX file:

https://drive.google.com/file/d/1G_aNAoy6gH2fi9Wp05TyK-ULt4oMGH7E/view?usp=sharing 

Thank you in advance!

[bcdobbs · Accepted solution]

Sorry! Now I see the PBIX I get what you're after.

Two way:

NewColumn2 = 

VAR CurrentProdCode = 'FACT'[ProductCode] 

VAR Result =
    CALCULATE (
        MAX ( ProductCategory[CategoryID] ),
        REMOVEFILTERS ( 'FACT' ),
        FILTER ( 'FACT', 'FACT'[ProductCode] = CurrentProdCode)
    )
    
RETURN
Result

Shorter code, Relies on table expansion (no not instantly obvious how it works) and I'm not sure of how fast it would be with a massive fact table.

Alternatively this also produces the same results:

NewColumn1 = 

VAR CurrentProdCode = 'FACT'[ProductCode] 

VAR PossibleCategories = 
    CALCULATETABLE (
        VALUES ( 'FACT'[ProductCategory] ),
        'FACT'[ProductCode] = CurrentProdCode,
        REMOVEFILTERS ('FACT')
    )
    

RETURN
	CALCULATE (
        MAX ( ProductCategory[CategoryID] ),
        TREATAS ( PossibleCategories, ProductCategory[ProductCategory] ),
        REMOVEFILTERS ( ProductCategory )
    )

[bhelou · Accepted solution]

Dear , 

i amde a test file and it worked as the desiered column data you want , 

kindly copy and paste the measure below and see if it works with you : 

MaxResult_Test = 
VAR CurrentCat = LOOKUPVALUE(ProductCategory[CategoryID], ProductCategory[ProductCategory], 'FACT'[ProductCategory])
VAR MaxCat = CALCULATE(MAX('FACT'[CurrentCategory]), ALLEXCEPT('FACT', 'FACT'[ProductCode]))
RETURN
    MAX(CurrentCat, MaxCat)

 Kindly accept as a solution if it works with you and a kudo will be greatfull . Thanks 

[bcdobbs]

Hi @ElenaGrom,

Your pbix didn't have the product table but I think something like this would work:

VAR CurrentProdCategory = 'FACT'[ProductCategory]

RETURN
	CALCULATE (
		MAX ( ProductCategory[CategoryID] ),
		ProductCategory[ProductCategory] = CurrentProdCategory
		)

You're creating a calculated column so their is an inherent row context. Eg 'FACT'[ProductCategory] will give the value for the current row. You can then use that to filter the ProductCategory table via calculate.

Ben

[ElenaGrom]

Hello, @bcdobbs 

I have updated the link to PBIX.

I have tried your solution, and I got the same wrong result as before.

Is there a way to get to the expected result?

Thank you in advance!

Regards,

Elena Gromova

[bhelou · Accepted solution]

Dear , 

i amde a test file and it worked as the desiered column data you want , 

kindly copy and paste the measure below and see if it works with you : 

MaxResult_Test = 
VAR CurrentCat = LOOKUPVALUE(ProductCategory[CategoryID], ProductCategory[ProductCategory], 'FACT'[ProductCategory])
VAR MaxCat = CALCULATE(MAX('FACT'[CurrentCategory]), ALLEXCEPT('FACT', 'FACT'[ProductCode]))
RETURN
    MAX(CurrentCat, MaxCat)

 Kindly accept as a solution if it works with you and a kudo will be greatfull . Thanks 

[ElenaGrom]

Many thanks, @bhelou !

Your solution is working for me!

[bcdobbs · Accepted solution]

Sorry! Now I see the PBIX I get what you're after.

Two way:

NewColumn2 = 

VAR CurrentProdCode = 'FACT'[ProductCode] 

VAR Result =
    CALCULATE (
        MAX ( ProductCategory[CategoryID] ),
        REMOVEFILTERS ( 'FACT' ),
        FILTER ( 'FACT', 'FACT'[ProductCode] = CurrentProdCode)
    )
    
RETURN
Result

Shorter code, Relies on table expansion (no not instantly obvious how it works) and I'm not sure of how fast it would be with a massive fact table.

Alternatively this also produces the same results:

NewColumn1 = 

VAR CurrentProdCode = 'FACT'[ProductCode] 

VAR PossibleCategories = 
    CALCULATETABLE (
        VALUES ( 'FACT'[ProductCategory] ),
        'FACT'[ProductCode] = CurrentProdCode,
        REMOVEFILTERS ('FACT')
    )
    

RETURN
	CALCULATE (
        MAX ( ProductCategory[CategoryID] ),
        TREATAS ( PossibleCategories, ProductCategory[ProductCategory] ),
        REMOVEFILTERS ( ProductCategory )
    )

[ElenaGrom · Accepted solution]

Many thanks, @bcdobbs !
Both variants are now working!

I have accepted this as a solution.

[bhelou]

Hello , 

The attached PBI file only containts Fact and Exchrates there is no product category at all in the file , please check 

[ElenaGrom]

Hello @bhelou 

I have updated the link