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.
Hi all!
I have a datamodel with 4 tables:
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?
Solved! Go to Solution.
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
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 )
)
)
@joshua1990
Would you please provide a screenshot to help us visualize the problem.
@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
@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.
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
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 |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |