Forum Discussion
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
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
- AnonymousNot applicableSorry. 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- calimero48Helper 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
- calimero48Helper 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