Forum Discussion

anandav's avatar
anandav
Skilled Sharer
6 years ago
Solved

Help with ALL function

Hi Experts,

 

I have the following measure:

Customer subset =
Var vAG = DISTINCT(
SELECTCOLUMNS(FILTER(ALL(Sales), MONTH(Sales[Sale Date]) IN ALLSELECTED('Analysis Group Calendar'[Month])),
"AG Customers", Sales[Sale Cust])
)
RETURN
CALCULATE(DISTINCTCOUNT(Sales[Sale Cust]), vAG)
 
This returns the correct value when there are NO relationship among the tables.
 
 
But when there are relationship with tables, and when there is a slicer selection of the Calendar table, the measure does not work.
 
I can understand that the Calendar slicer is filtering the Sales.
Eventhough there is ALL(Sales) I have no way of adding another condition for ALL(Calendar) in the above SELECTCOLUMNS statement.
 
How can I rewrite the above measure so it will ignore any filters in Sales and Calendar?
 
Your help will be much appreciated.
 
Thanks,
Anand
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi anandav 

    I create a measure which show the same result as your Customer subset measure when there is no relationship among tables.

    Measure =
CALCULATE (
    DISTINCTCOUNT ( sales[sale cust] ),
    FILTER (
        ALL ( sales ),
        sales[sale date] <= MAX ( 'filter date table'[Date] )
            && sales[sale date] >= MIN ( 'filter date table'[Date] )
    )
)

     

    Also this measure won't change with the slicer from "calendar[date]".

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

7 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    6 years ago

    anandav 

    Not too sure, but I believe the new REMOVEFILTERS can remove all filters: REMOVEFILTERS ().

    • anandav's avatar
      anandav
      Skilled Sharer
      6 years ago

      PaulDBrown,

      Thanks for the suggestion. But FILTER doesn't accept REMOVEFILTERS()

      From DAX.guide:

      REMOVEFILTERS is an alias for ALL, but it can be used only as a CALCULATE modifier and not as a table expression like ALL

      • amitchandak's avatar
        amitchandak
        Super User
        6 years ago

        Can it be done calculate by using cross filter or cross join

         

        CALCULATE(DISTINCTCOUNT(Sales[Sale Cust]), vAG,crossfilter(<>,<>,none))

         

         

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    6 years ago

    Hi,

    It will be a lot easier to help if you share some data, explain the business context and also show the expected result.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi anandav 

    I create a measure which show the same result as your Customer subset measure when there is no relationship among tables.

    Measure =
CALCULATE (
    DISTINCTCOUNT ( sales[sale cust] ),
    FILTER (
        ALL ( sales ),
        sales[sale date] <= MAX ( 'filter date table'[Date] )
            && sales[sale date] >= MIN ( 'filter date table'[Date] )
    )
)

     

    Also this measure won't change with the slicer from "calendar[date]".

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    • anandav's avatar
      anandav
      Skilled Sharer
      6 years ago

      v-juanli-msft ,

      Thanks a lot for the help and really appreciate it. That works perfectly. :smileyhappy: