topic A function 'MAX' has been used in a True/False expression that is used as a table filter expression. in DAX Commands and Tips

A function 'MAX' has been used in a True/False expression that is used as a table filter expression.

datagoblin77 — Fri, 31 May 2024 14:25:36 GMT

I am trying to create a card that shows the reduction in emissions for a school. I created a measure for total beef purchases:

Total Emissions:= SUM(tSustainability_FactBeefEmissions[CO2 (tonnes)])

Then I created a percentage measure:

Emission difference (pecentage):=
Var EarliestData = CALCULATE([Total Emissions], tSustainability_FactBeefEmissions[Order] = MIN(tSustainability_FactBeefEmissions[Order]))
VAR LatestData = CALCULATE([Total Emissions], tSustainability_FactBeefEmissions[Order] =Max(tSustainability_FactBeefEmissions[Order]))

RETURN
FORMAT( (LatestData - EarliestData)/EarliestData, "0%")

The measure returns the right value in visual studio. When I deploy the solution in pbi desktop I get the message that a MIN has been used in a true/false expression. How do I make dax pick the value in the emission column that is corresponding to the lowest value in the order column?

Re: A function 'MAX' has been used in a True/False expression that is used as a table filter expression.

BeaBF — Fri, 31 May 2024 14:30:08 GMT

datagoblin77 try with:

Emission difference (percentage):=
VAR EarliestOrder = CALCULATE(MIN(tSustainability_FactBeefEmissions[Order]))
VAR EarliestEmission = CALCULATE([Total Emissions], tSustainability_FactBeefEmissions[Order] = EarliestOrder)

VAR LatestOrder = CALCULATE(MAX(tSustainability_FactBeefEmissions[Order]))
VAR LatestEmission = CALCULATE([Total Emissions], tSustainability_FactBeefEmissions[Order] = LatestOrder)

RETURN
FORMAT( (LatestEmission - EarliestEmission)/EarliestEmission, "0%")

Re: A function 'MAX' has been used in a True/False expression that is used as a table filter expression.

mark_endicott — Fri, 31 May 2024 14:51:13 GMT

datagoblin77 - By putting the MIN and MAX next to an = you turn it into a TRUE/FALSE expression.

You should try this:

VAR _min = MIN ( tSustainability_FactBeefEmissions[Order] ) VAR _max = MAX ( tSustainability_FactBeefEmissions[Order] ) VAR min_Emission = CALCULATE ( [Total Emissions], KEEPFILTERS ( tSustainability_FactBeefEmissions[Order] = _min ) ) VAR max_Emission = CALCULATE ( [Total Emissions], KEEPFILTERS ( tSustainability_FactBeefEmissions[Order] = _max ) ) RETURN DIVIDE ( ( max_Emission - min_Emission ), min_Emission, 0 )

KEEPFILTERS is used to maintain this filter context, but not overwrite any others, which if you use this calculation elsewhere will prevent a confusing output.

DIVIDE is more optimal than using "/" and it helps with divide by 0 errors which "/" will not.

I have also removed your FORMAT expression, because this is turning the figure presented into a string. By using the Power BI UI to make this a % you will have much more freedom, and can use this calculation for conditional formatting etc. You can do this by selecting the measure and selecting Percentage from the Format options on Measure Tools:

If this works for you, please mark it as the solution!

Re: A function 'MAX' has been used in a True/False expression that is used as a table filter expression.

datagoblin77 — Fri, 31 May 2024 16:03:53 GMT

Thank you!

Re: A function 'MAX' has been used in a True/False expression that is used as a table filter expression.

mark_endicott — Fri, 31 May 2024 16:24:12 GMT

Happy to help!