AllExcept with Filtering

[shaunbrookes]

I'm hoping someone can assist me here. I'm coming from Tableau so I'm not sure what I'm trying to do is achievable in PowerBI.

What i'm trying to do is the following:

Step 1) Dashboard User filters using slicers (various metrics)

Step 2) I then want to Calculate the minimum date per customer based on the filtering done in Step 1). In tableau this was done by adding all the filters to context and then calculating a fixed LOD calculation on CustomerID. The important part here was that the filters were run first and then the fixed calculation was calculated afterwards.

Step 3) Use the fixed calculation from step 2) in a new column

What I've managed so far in power BI:

1) Slicers filter the data fine

2) Tried to calculate the fixed calculation using: 

Fixed Date= calculate(min(Query1[Date]),ALLEXCEPT((Query1),Query1[CustomerID]))

The problem I have here is it correctly calculates the minimum date for each customer but not taking into account the slicers filtered in step 1). I understand this is because i've used the ALLEXCEPT statement but I don't know what else to use as I need this fixed on a customer level. I tried adding in other filters into the ALLEXCEPT, for example:

Fixed Date= calculate(min(Query1[Date]),ALLEXCEPT((Query1),Query1[CustomerID], Query1[Event]))

but then this fixes the minimum date on a customer and event level. (I only want it on customer level)

So basically in summary my question is how can I run all slicer filters first and then do a fixed calculation on CustomerID taking into account all the filters that have already run?

Thanks for the assistance

[Anonymous]

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 

[shaunbrookes]

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?

Thanks

[Anonymous]

Ok 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" )

[shaunbrookes]

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?

[shaunbrookes]

Just bumping this up incase anyone can assist

[Anonymous]

First Order =
VAR _FirstDate =
    CALCULATE (
        MIN ( FactInternetSales[OrderDate] ),
        ALLSELECTED ( )
    )
RETURN
    IF ( FactInternetSales[OrderDate] = _FirstDate, "yes", "no" )

Try this measure, if it doesnt work then you will need to use the ALLSELECTED measure but adding any columns used as slicers to the exceptions