Help with ALL function

[anandav]

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

[v-juanli-msft · Accepted solution]

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]

@v-juanli-msft ,

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

[Ashish_Mathur]

Hi,

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

[PaulDBrown]

@anandav 

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

[anandav]

@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]

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

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

[anandav]

@amitchandak,

That doesn't work.

I need a way to remove filters in Sales and Calendar table in the SelectCOlimns statement.