Forum Discussion

calimero48's avatar
calimero48
Helper II
6 years ago
Solved

Filter issue (RemoveFilter on a Column)

Hi   I am using SSAS Tabular SQLserver 2017.   I want to create a measure on a table defined as   Report ID,Product1,Product2,DateReport ( And more fields)   I want a mesure that count Produ...
  • calimero48's avatar
    calimero48
    6 years ago

    Hi

     

    I found my mistake

     

    var NbProduct1= IF(ISFILTERED(report[PRODUCT_DETAILED_1__C]);
    CALCULATE(count(report[Id]);ALLSELECTED(report[PRODUCT_DETAILED_1]));BLANK())

    var NbProduct2=CALCULATE(count(report[Id]);ALL(report[PRODUCT1]);FILTER(ALL(report[PRODUCT2]);report[PRODUCT2] IN VALUES(report[PRODUCT1]))

    Return CRNbProduct2+NbProduct1

     

    Regards

calimero48's avatar
calimero48
Helper II
6 years ago

Hi

 

 

Sorry if my request was not clear.

 

We can produce the problem with only one table.

 

ID,Product1,Product2,DateReport ( And more fields)

1 ,Coffe      ,Tea          ,20200401

2 ,Milk       ,Coffe       ,20200420

 

We filter on product 1 for Coffe. The measure should give 2 ( 1 coffe for ID, Product 1 and 1 Coffe for Id 2 Product2)

 

If we filter on product for Tea we should have 1 ( ID=1 Product 2= Tea)

 

I hope it's more clear

 

Regards

 

 

 

calimero48's avatar
calimero48
Helper II
6 years ago

Hi

 

I found my mistake

 

var NbProduct1= IF(ISFILTERED(report[PRODUCT_DETAILED_1__C]);
CALCULATE(count(report[Id]);ALLSELECTED(report[PRODUCT_DETAILED_1]));BLANK())

var NbProduct2=CALCULATE(count(report[Id]);ALL(report[PRODUCT1]);FILTER(ALL(report[PRODUCT2]);report[PRODUCT2] IN VALUES(report[PRODUCT1]))

Return CRNbProduct2+NbProduct1

 

Regards