topic Re: Wrong total with IF MAX measure in DAX Commands and Tips

Wrong total with IF MAX measure

joshua1990 — Mon, 27 Mar 2023 11:55:23 GMT

Hi all!

I have a datamodel with 4 tables:

2 dimensional tables
- Calendar (Date, Year, Month)
- Master Data (Article)
2 transactional tables
- Past Orders (Article, Date, Value)
- Open Orders (Article, Date, Value)

Both transactional tables are related to both dimensional tables.

Now I have build a simple matrix and a measure with the following logic:

Orders = VAR _Orders = [Order Value] RETURN IF(MAX('Open Orders'[Date]) > MAX('Past Orders'[Date]), _Orders , 0)

The matrix has Months as columns. The result of the measure is displayed correctly for each month.

But the sum / total is 0 on the right.

What is the issue here?

Re: Wrong total with IF MAX measure

BeaBF — Mon, 27 Mar 2023 12:03:05 GMT

joshua1990 Hi!

try this formula:

Orders =
VAR _Orders = [Order Value]
VAR _MaxOpenDate = MAX('Open Orders'[Date])
VAR _MaxPastDate = MAX('Past Orders'[Date])
RETURN IF(_MaxOpenDate > _MaxPastDate, _Orders, IF(ISBLANK(_MaxOpenDate) || ISBLANK(_MaxPastDate), BLANK(), 0))

This measure will return the order value for all months, but only include the value in the calculation if the 'Open Orders' table has a later date than the 'Past Orders' table. It also includes a check for blank values in case there are missing dates in either of the tables. This should give you the correct sum/total for the 'Orders' measure.

BBF

Re: Wrong total with IF MAX measure

joshua1990 — Mon, 27 Mar 2023 13:49:52 GMT

BeaBF : Thanks a lot for your support, but still the same issue. No SUM / Total on the right. Still a 0.

Bot tables are linked 1:n to the calendar table.

Re: Wrong total with IF MAX measure

tamerj1 — Mon, 27 Mar 2023 14:26:30 GMT

Hi joshua1990

please try

Orders =
SUMX (
VALUES ( 'Calendar'[YearMonth] ),
CALCULATE (
VAR _Orders = [Order Value]
VAR _MaxOpenDate =
MAX ( 'Open Orders'[Date] )
VAR _MaxPastDate =
MAX ( 'Past Orders'[Date] )
RETURN
IF ( _MaxOpenDate > _MaxPastDate, _Orders, 0 )
)
)

Re: Wrong total with IF MAX measure

joshua1990 — Mon, 27 Mar 2023 17:38:55 GMT

tamerj1 : I really don't know why, but still the same issue....

Re: Wrong total with IF MAX measure

tamerj1 — Tue, 28 Mar 2023 05:55:39 GMT

joshua1990
Would you please provide a screenshot to help us visualize the problem.

Re: Wrong total with IF MAX measure

BeaBF — Tue, 28 Mar 2023 07:24:28 GMT

joshua1990

The issue could be related to the context in which the measure is being evaluated. When you add up the values of the measure, the total might be showing as zero due to the filter context applied on the table.

To fix this issue, you can try using the "ALL" function to remove the filter context from the table and evaluate the measure across all rows.

Here's an updated measure that should work:

Orders =
VAR _Orders = [Order Value]
RETURN
IF(
MAX('Open Orders'[Date]) > MAX('Past Orders'[Date]),
_Orders,
0
)

Total Orders =
CALCULATE(
[Orders],
ALL('Calendar'[Month])
)

BBF