Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
First let me describe the situation:
I have a table with "Order Date" , "Supply Date" and "Sum" columns, and a date table.
I created a relationship between "Supply Date" and the date table. and i need this relationship for other parts of my report.
Now I want to display the open orders of every month and keep filters like: customer, part ,status etc..
I tried few methods using FILTER() and ALL():
1)
Order Sum Open At The Time = VAR minDate = MIN('Calender'[Date]) RETURN VAR maxDate = MAX('Calender'[Date]) RETURN CALCULATE(SUM('ORDERS'[Sum]),ALL('ORDERS'[Supply Date]), FILTER('ORDERS', 'ORDERS'[Order Date])<=maxDate && 'ORDERS'[Supply Date]>=minDate ) )
This had no effect at all on the FILTER() function (the orders table was still filterd via the relationship I mentioned).
so i tried:
2)
Order Sum Open At The Time = VAR minDate = MIN('Calender'[Date]) RETURN VAR maxDate = MAX('Calender'[Date]) RETURN CALCULATE(SUM('ORDERS'[Sum]), FILTER(CALCULATETABLE('ORDERS',ALL('ORDERS'[Suply Date])), 'ORDERS'[Order Date])<=maxDate && 'ORDERS'[Supply Date]>=minDate ) )
again, this had no effect at all.
The last thing was to use ALLEXCEPT():
3)
Order Sum Open At The Time = VAR minDate = MIN('Calender'[Date]) RETURN VAR maxDate = MAX('Calender'[Date]) RETURN CALCULATE(SUM('ORDERS'[Sum]), FILTER( FILTER(ALLEXCEPT('ORDERITEMS',..,...//here there is a long list of every column in my report except "Supply Date"), , 'ORDERS'[Order Date])<=maxDate && 'ORDERS'[Supply Date]>=minDate ) )
This did work but requires me to type a list of all columns in my report except "Supply Date", this is not very good since my report is very big.
So my question is how can I use filter while keeping filters on all columns except of one column (in this case supply date)?
Please help me figure this out.
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 |
---|---|
103 | |
101 | |
81 | |
78 | |
66 |
User | Count |
---|---|
122 | |
110 | |
94 | |
82 | |
77 |