Remove any Set Filters but set filter for the following ?

[Anonymous]

Hi Experts,

Need help in DAX syntax to remove all the filters but set filter to specified including calendar.

I am planning to create some static KPI cards irrespective of filters set but set for specific fitler type.

Tables:  TE_ (fact table), Types (with option 1 - 5) is a dim table and I have another 3 Dim tables

Sales_did is the column of TE_

Type2_Sales = CALCULATE([CountOfSales_Id],REMOVEFILTERS(TE_),FILTER(Type,Type[Types]=2))

Intention is to create COUNT OF SALES a static KPI card when Type = 2 but REMOVE ALL other filters except calendar dates in between. 

Type2_Sales = CALCULATE([CountOfSales_Id],REMOVEFILTERS(TE_)) works but that that is not what I want, I think this is data when you remove every set filter right?

Help is appreciated.

[Anonymous]

@tamerj1 Thank you for the great help and time, You are awesome. 

I have some final issues of The syntax for '=' is Incorrect .. I will some study to try to fix this,

[tamerj1]

@Anonymous 

If it is a string then use

CALCULATE (
    SUM ( ResellerSales_USD[SalesAmount_USD] ),
    ALLEXCEPT ( TE_, TE_[Date] ),
    TE_[Type] = "2"
)

If it is a number then use

CALCULATE (
    SUM ( ResellerSales_USD[SalesAmount_USD] ),
    ALLEXCEPT ( TE_, TE_[Date] ),
    TE_[Type] = 2
)

[Anonymous]

@tamerj1 Thanks a lot. It worked. The mistake is mine, the measure name got repeated twice with 2 times =. It's been a great help indeed. 

[Anonymous]

@tamerj1  

Thanks a lot helping me. I am troubled with The Syntax for '=' is incorrect, if not too much trouble when you have time, suggest some idea or i will do some reading and see if I can sort this out. 

[tamerj1]

@Anonymous 

Use ALLEXCEPT as you have to keep the filters on the columns that provide the connection with other dim tables like type and date depending your requirement. 

[Anonymous]

@tamerj1  

Thank you but I am not sure, how I can pass multiple table and parameters to it

Eample Provided:

= CALCULATE(SUM(ResellerSales_USD[SalesAmount_USD]), ALLEXCEPT(DateTime, DateTime[CalendarYear]))

but in my case, I need pass ALLEXCEPT(DateTable, DateTable[Date] but Also

1. Type, Type[Types]  and Also ensure I set the filter for

2. Type[types] = 2) 

How can I combine all these ? 

Thanks again.

[tamerj1]

@Anonymous 

The DateTable[Date] is connected to a date column in the fact table. Let's say TE_[Date]. Similarly, the Type[Types] column is connected to the fact table through a column let's sat TE_[Type] then the ALLEXCEPT should be

ALLEXCEPT ( TE_, TE_[Date], TE_[Type] )

[Anonymous]

@tamerj1  Thank you for getting back, I did that part. But when I hard code TE_[Type] = '2' , I am getting syntax error. ALLEXCEPT ( TE_, TE_[Date], TE_[Type]='2' ) The final step. Again, thanks a ton for the following up and trying to help. As I need the KPI when TE_[Type]='2'  

Actually Type[Type]= '2' is all I need for this filter

[tamerj1 · Accepted solution]

@Anonymous 

CALCULATE (
    SUM ( ResellerSales_USD[SalesAmount_USD] ),
    ALLEXCEPT ( TE_, TE_[Date] ),
    TE_[Type] = '2'
)