cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
RGI Member
Member

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

Accepted Solutions
RGI Member
Member

Re: Create calculated measure from unrelated table with filters

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
Super User I
Super User I

Re: Create calculated measure from unrelated table with filters

Could you share your datamodel? some sample data?

I dont userstand what you want to do if your tables are unrelated?

RGI Member
Member

Re: Create calculated measure from unrelated table with filters

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

 

Super User I
Super User I

Re: Create calculated measure from unrelated table with filters

Perhaps an Expected result? 
I still dont understand what you are trying to visualize...

RGI Member
Member

Re: Create calculated measure from unrelated table with filters

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.

Super User I
Super User I

Re: Create calculated measure from unrelated table with filters

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

RGI Member
Member

Re: Create calculated measure from unrelated table with filters

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

RGI Member
Member

Re: Create calculated measure from unrelated table with filters

RGI Member
Member

Re: Create calculated measure from unrelated table with filters

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

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors