Forum Discussion

shaunbrookes's avatar
shaunbrookes
Frequent Visitor
8 years ago

AllExcept with Filtering

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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      shaunbrookes
      Frequent Visitor
      8 years ago
      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's avatar
        Anonymous
        Not applicable
        8 years ago

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