Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hello,
Need help with DAX function please.
Below is a pic of my data source, I'm interested in populating the field "Is Maximum Date" with 1 / 0 values.
Row Number | Date | Transaction Number | Customer | Customer Category | Is Maximum Date |
1 | 6-Mar-18 | 100 | A-John | A | |
2 | 6-Mar-18 | 200 | B-Kim | B | |
3 | 7-Mar-18 | 300 | A-John | A | |
4 | 7-Mar-18 | 400 | A-John | A |
I want that field to be calculated according to filters applied by the user -
If user select in filter Customer = "A-John", I want to see Is Maximum Date =1 for row 3 and 4
If user select in filter Customer Category = "B", I want to see Is Maximum Date =1 for row 2
and so on...
How can I do this please?
Thanks!
Hi @Yael,
Please check out a demo here.
1. You need dimensional tables in your scenario.
2. Two slicers.
3. The measure could be like this:
Measure = VAR maxdate = CALCULATE ( MAX ( 'Table1'[Date] ), ALL ( 'Table1'[Row Number] ) ) RETURN IF ( MIN ( 'Table1'[Date] ) = maxdate, 1, 0 )
Best Regards,
Dale
Thanks @v-jiascu-msft. Tried creating the measure in the DateDim:
IsMaxDate =
VAR maxdate =
CALCULATE (MAX(dim[Date]), ALL(dim[DatePK]))
RETURN
IF (MAX(dim[Date]) = maxdate, 1, 0 )
When I drag this measure to my fact table, I see that it's duplicating the rows for dates that don't exist in the fact + all rows are getting 1 in "IsMaxDate"
HI @Yael
Try this one
IsMaxDate = VAR maxdate = CALCULATE ( MAX ( dim[Date] ), ALLSELECTED () ) RETURN IF ( MAX ( dim[Date] ) = maxdate, 1, 0 )
Now I recieve 1 in one row only, but the fact still displays rows for all dates (even those which don't exist in the fact):
Any other ideas on how we can create a general function that calculates MAX and Min according to values in filters / slicers?
Hi Yael,
On the one hand, you can't create calculated columns depending on report filters because the values of these columns are calculated when the data is loaded before the user select the filter.
On the other hand, if you want to fill your column with 1/0, where 1 is once per user we can do it:
Is Maximum Date =
VAR CUSTOMER = MAX(Customer)
RETURN
IF(
Date = CALCULATE(
MAX(Date);
ALL(TransactionsTable);
Customer = CUSTOMER
);
1;
0
)
Hope this can help you,
Jorge.
Thanks @GeorgeBuster! the above DAX ereturns an error message, not specifing where is the issue...
Could you share a screenshot? Maybe we can find the error. Thanks.
User | Count |
---|---|
119 | |
78 | |
59 | |
52 | |
48 |
User | Count |
---|---|
171 | |
117 | |
61 | |
59 | |
53 |