Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
calimero48
Helper II
Helper II

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

 

 

 

 

 

1 ACCEPTED SOLUTION

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

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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

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

 

 

 

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.