Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |