Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 =
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.
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!
Solved! Go to 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 )
)
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
Many thanks, @bcdobbs !
Both variants are now working!
I have accepted this as a solution.
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
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
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
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 )
)
Many thanks, @bcdobbs !
Both variants are now working!
I have accepted this as a solution.
Hello ,
The attached PBI file only containts Fact and Exchrates there is no product category at all in the file , please check
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |