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,
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.
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 |
---|---|
107 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |