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.
I have four unrelated tables:
a) Store (column "Store Number")
b) Calendar (column "Sales Date")
c) SKU (column "SKU Code")
d) Sales (columns "Store Number", "Sales Date", "SKU Code" and "Sales Quantity")
I have slicers on the Calendar and SKU tables
I need to list all stores with total "Sales Quantity" for each store and at the same time to limit the sales quantity to the two slicers mentioned above. Basically I need to list these columns:
1) Store Number - from the Store table (no filtering from Slicers)
2) Store Name - from the Store table (no filtering from Slicers)
3) Total Quantity of Sales for the Store - calculated measure filtered by Calendar and SKU slicers
So my question is, what is the DAX required to create the calculated measure?
Solved! Go to Solution.
Two solutions were posted on StackExchange, shown here to help anyone else with the same issue. In order to do this you must have relationships intact and active.
Qty by Store = SUM ( Sales[Sates Quantity] ) + 0
Alexis Olson added this comment:
This works because DAX calculates BLANK() + 0 = 0
Qty by Store =
VAR res = sum(Sales[Sales Quantity])
RETURN IF (ISBLANK(res), 0, res)
Nick Krasnov added these comments:
There is no need for USERELATIONSHIP(). Relationship Store - Sales is already active. The reason why the number of stores changes in the table visual is because when there is no sale for a particular store Qty by store measure returns BLANK and those BLANKs get filtered out by the table.
Could you share your datamodel? some sample data?
I dont userstand what you want to do if your tables are unrelated?
Here is some sample data
Store
Store Number | Store name |
1021 | GATESHEAD METRO |
1052 | NINEWELLS |
1107 | ABBEY WOOD STATION |
1111 | ABERDEEN AIRPORT |
1145 | ABERDEEN RYL INF |
1201 | ADASTRAL PARK |
1221 | AXA |
1229 | BALDOCK |
1236 | CAMBRIDGE RAIL |
1261 | BARKING |
Calendar
Sales Date | Sales Week | Sales Month | Sales Year |
15/08/2019 | 50 | 12 | 18 |
14/08/2019 | 50 | 12 | 18 |
13/08/2019 | 50 | 12 | 18 |
12/08/2019 | 50 | 12 | 18 |
11/08/2019 | 50 | 12 | 18 |
10/08/2019 | 49 | 12 | 18 |
09/08/2019 | 49 | 12 | 18 |
08/08/2019 | 49 | 12 | 18 |
07/08/2019 | 49 | 12 | 18 |
06/08/2019 | 49 | 12 | 18 |
05/08/2019 | 49 | 12 | 18 |
04/08/2019 | 49 | 12 | 18 |
SKU
SKU Code | SKU Name |
15 | DIRECT RED WESTON G |
33 | DIARY OF A YOUNGFRANK A |
40 | ANNES SONG NOLAN A |
47 | 40001 BABY NAMES STAFFORD D |
49 | WALL AND PIECE BANKSY |
57 | HISTORY OF HISTOBURROW |
60 | THREE CUPS OF TEMORTENSON |
68 | LIBERAL FASCISM GOLDBERG |
83 | DICKENS ACKROYD |
96 | I NEVER KNEW THAT ABOUT LONDON |
Sales
Store Number | Sales Date | SKU Code | Sales Quantity |
1021 | 15/08/2019 | 15 | 5 |
1021 | 14/08/2019 | 15 | 8 |
1021 | 13/08/2019 | 33 | 7 |
1052 | 12/08/2019 | 33 | 12 |
1107 | 11/08/2019 | 33 | 22 |
1107 | 10/08/2019 | 40 | 4 |
1111 | 09/08/2019 | 47 | 8 |
1145 | 08/08/2019 | 49 | 9 |
1201 | 07/08/2019 | 57 | 11 |
1221 | 06/08/2019 | 60 | 5 |
1229 | 05/08/2019 | 68 | 8 |
1236 | 04/08/2019 | 83 | 3 |
1261 | 03/08/2019 | 96 | 21 |
Perhaps an Expected result?
I still dont understand what you are trying to visualize...
I need to list all stores regardless of the date or SKU filter, and for each store I need the total sales for the date period chosen and for SKUs chosen
I know I can relate Stores to Sales, but then a date slicer would remove stores from the list that don't have sales.
PowerBI needs a relationship between tables in orde to combine it...
Just answering your question and ignoring the fact if it is actually removing values.
You could create a NON-Active relationship between the tables (just go to relationship properties and untick active...)
Then you create a measure ex:
CALCULATE(SUM(SALES), USERELATIONSHIP(TABLE1, TABLE2))
Not 100% sure if this would work, but worth a try.
Robbe
Thanks for the suggestion Robbe
Unfortunately when I do this I eliminate all stores that don't have sales. I need to list all stores even those with zero sales
Here is a link to a sandbox pbix:
https://1drv.ms/u/s!AjQiTPc3xudmoaVpJGuayxfjrt3a-w?e=yiO6zn
Two solutions were posted on StackExchange, shown here to help anyone else with the same issue. In order to do this you must have relationships intact and active.
Qty by Store = SUM ( Sales[Sates Quantity] ) + 0
Alexis Olson added this comment:
This works because DAX calculates BLANK() + 0 = 0
Qty by Store =
VAR res = sum(Sales[Sales Quantity])
RETURN IF (ISBLANK(res), 0, res)
Nick Krasnov added these comments:
There is no need for USERELATIONSHIP(). Relationship Store - Sales is already active. The reason why the number of stores changes in the table visual is because when there is no sale for a particular store Qty by store measure returns BLANK and those BLANKs get filtered out by the table.
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 |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |