Forum Discussion
AllExcept with Filtering
if you want the minimum date for a customer you can just use the filter context of the slicers/visuals in a page.
Aka if you have a table with customers and then the measure MIN ( Query1[Date] ) which you place in the table. Then add the slicers and the value should be correct for each customer within the fitler conxtext defined by the slicers.
IF this doesnt solve your problem can you please post an example expected result and what you are starting with
- Thanks for the response. Sorry I've just left my PC so can't show a detailed example now but I'll try explain typing on my phone. The reason I need the minimum date fixed on a customer level is so that I can use that measure in a calculated column afterwards. So I'm trying to:
1) filter first and then calculate min date fixed per customer
2) use that fixed calculation in a column calculation to say if transaction date = fixed date then "yes" else "no"
3) use the column calculation to filter and only keep the first transaction per customer ("yes").
Hope that makes sense?
ThanksOk so as you want to filter on this a measure cant be used so a calculated column is needed.
I have made a column that does what you described an AdventureWorks model. Hopefully you will be able to transfer it to yours.
What it does is calculate the minimum date for the current customer in the row context then for each row it compares the current row context date vs this date and then depending on this comparision it returns yes or no
First Order = VAR _FirstDate = CALCULATE ( MIN ( FactInternetSales[OrderDate] ), ALLEXCEPT ( FactInternetSales, DimCustomer[CustomerKey] ) ) RETURN IF ( FactInternetSales[OrderDate] = _FirstDate, "yes", "no" )Thanks again for the response, unfortunately this is not quite giving me what I'm looking for. The issue I still have here is if the user makes changes to the slicers, I need the _FirstDate variable to refect these changes and update accordingly. So for example say we had a country field, if the user doesn't select anything, then this solution would work and provide me with the minimum date per customer, but if the user selects a specific country, I'd need _FirstDate to provide me with the minumum date per customer based on only the country(s) selected. (currently its still taking everything into account and ignoring the slicers) Hope that makes sense?
