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

 

 

  • shaunbrookes's avatar
    shaunbrookes
    Frequent Visitor
    8 years ago

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

      Just bumping this up incase anyone can assist

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago
        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