topic Re: Filter issue (RemoveFilter on a Column) in DAX Commands and Tips

Filter issue (RemoveFilter on a Column)

calimero48 — Fri, 24 Apr 2020 09:09:51 GMT

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

Anonymous — Fri, 24 Apr 2020 11:50:58 GMT

Sorry. I've tried to understand the setup but not able. Can you please do it in a clearer way? Also, you have to show us the data model.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best
D

calimero48 — Fri, 24 Apr 2020 18:11:17 GMT

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 — Tue, 28 Apr 2020 08:46:18 GMT

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