cancel
Showing results for
Did you mean:
Highlighted
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
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

This works because DAX calculates BLANK() + 0 = 0

Qty by Store =
VAR res = sum(Sales[Sales Quantity])
RETURN IF (ISBLANK(res), 0, res)

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.

8 REPLIES 8
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?

Member

## Re: Create calculated measure from unrelated table with filters

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

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

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

## 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

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

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

This works because DAX calculates BLANK() + 0 = 0

Qty by Store =
VAR res = sum(Sales[Sales Quantity])
RETURN IF (ISBLANK(res), 0, res)

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.

Announcements

#### 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?

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

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