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 Product in Product2 and Product1 when they are filered in Product1 ( Other Filter should be keep of course)

 

My measure:

 

NbProduct:=

var Liste= CONCATENATEX(VALUES(report[PRODUCT1]);report[PRODUCT1];",")


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(VALUES(report[PRODUCT2]);report[PRODUCT2] in {Liste}))

Return CRNbProduct2+NbProduct1

 

The problem, I got that NbProduct2 never gave me the correct number.

 

I have done some testing:

NbProduct2=CALCULATE(count(report[Id]);ALL(report[PRODUCT1]))

If the one or more product are selected, I got the correct number (Number of Rows in the table)

 

NbProduct2=CALCULATE(count(report[Id]);ALL(report[PRODUCT1]);FILTER(VALUES(report[PRODUCT2]);report[PRODUCT2] in {"Coffe"})

If the one or more product are selected, I got blank;

If All product are selected in Product1, I got the number of Coffe in Product2.

 

I do not understand why. The DAX function removefilter is not available for me.

 

May be it's trivial but I do not see.

 

Your help is welcome

Regards

 

 

 

 

 

  • 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

3 Replies

    • calimero48's avatar
      calimero48
      Helper II

      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

        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