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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RGI
Helper III
Helper III

Create calculated measure from unrelated table with filters

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?

 

1 ACCEPTED 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.

View solution in original post

8 REPLIES 8
RobbeVL
Impactful Individual
Impactful Individual

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 NumberStore name
1021GATESHEAD METRO
1052NINEWELLS
1107ABBEY WOOD STATION
1111ABERDEEN AIRPORT
1145ABERDEEN RYL INF
1201ADASTRAL PARK
1221AXA
1229BALDOCK
1236CAMBRIDGE RAIL
1261BARKING

 

Calendar

Sales DateSales WeekSales MonthSales Year
15/08/2019501218
14/08/2019501218
13/08/2019501218
12/08/2019501218
11/08/2019501218
10/08/2019491218
09/08/2019491218
08/08/2019491218
07/08/2019491218
06/08/2019491218
05/08/2019491218
04/08/2019491218

 

SKU

SKU CodeSKU Name
15DIRECT RED WESTON G
33DIARY OF A YOUNGFRANK A
40ANNES SONG NOLAN A
4740001 BABY NAMES STAFFORD D
49WALL AND PIECE BANKSY
57HISTORY OF HISTOBURROW
60THREE CUPS OF TEMORTENSON
68LIBERAL FASCISM GOLDBERG
83DICKENS ACKROYD
96

I NEVER KNEW THAT ABOUT LONDON

 

Sales

Store NumberSales DateSKU CodeSales Quantity
102115/08/2019155
102114/08/2019158
102113/08/2019337
105212/08/20193312
110711/08/20193322
110710/08/2019404
111109/08/2019478
114508/08/2019499
120107/08/20195711
122106/08/2019605
122905/08/2019688
123604/08/2019833
126103/08/20199621

 

RobbeVL
Impactful Individual
Impactful Individual

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.

RobbeVL
Impactful Individual
Impactful Individual

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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