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, and thanks in advance for your help 😄
What i'm trying to do with this measure
is to exclude from my analysis those products that have no sales or quantity in any given year. To do this i'm using two previously calculated measures as filters: Net Sales from 2018 and 2019 ([PY Net Sales] and [CY Net Sales] respectively), and work fine.
The issue is, when I drop this newly calculated measure in a visual it returns nothing. Only an empty box.
The data look something like the one below.
Thanks again!
Year | Product | Net Sales | Quantity | Brand | BU |
2018 | A1000 | 1000 | 10 | Red | Alpha |
2018 | A1001 | 2000 | 200 | Red | Alpha |
2018 | A1002 | 100 | 100 | Green | Alpha |
2018 | A1003 | 5000 | 20 | Green | Alpha |
2018 | A1004 | 10000 | 1000 | Blue | Beta |
2018 | A1005 | 1000 | 50 | Blue | Beta |
2019 | A1000 | 1500 | 15 | Red | Alpha |
2019 | A1001 | 800 | 150 | Red | Alpha |
2019 | A1002 | 0 | 0 | Green | Alpha |
2019 | A1003 | 7500 | 40 | Green | Alpha |
2019 | A1004 | 10100 | 1500 | Blue | Beta |
2019 | A1005 | 7500 | 25 | Blue | Beta |
2019 | A1000 | -1000 | 10 | Red | Alpha |
Solved! Go to Solution.
Hey there,
Without having seen the actual data, experience tells me the issue is that there aren't any rows in your table that fulfill the filter condition. Sintactically, the formula looks correct.
Maybe you could try using ISBLANK ( [PY Net Sales] ) instead of checking whether it's equal to 0.
Hope this helps.
Alex
Hi @Anonymous
You can try something like this.
Measure = VAR _allProductYear = ALL( 'Table'[Product], 'Table'[Year] ) VAR _groupProductYear = GROUPBY( _allProductYear, 'Table'[Product], 'Table'[Year] ) VAR _filterProductYear = FILTER( _groupProductYear, CALCULATE( SUM( 'Table'[Quantity] ) ) = 0 ) VAR _filterProduct = CALCULATETABLE( VALUES( 'Table'[Product] ), _filterProductYear ) RETURN CALCULATE( SUM( 'Table'[Quantity] ), EXCEPT( VALUES( 'Table'[Product] ), _filterProduct ) )
Hey there,
Without having seen the actual data, experience tells me the issue is that there aren't any rows in your table that fulfill the filter condition. Sintactically, the formula looks correct.
Maybe you could try using ISBLANK ( [PY Net Sales] ) instead of checking whether it's equal to 0.
Hope this helps.
Alex
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 |
---|---|
56 | |
27 | |
24 | |
14 | |
9 |
User | Count |
---|---|
77 | |
61 | |
47 | |
17 | |
12 |