cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wenners68 Regular Visitor
Regular Visitor

Subquery

Hi,

I want to create a measure that sums sales figures based on customers who bought something this edition, but not last.

The edition is just a book edition and not relevant to a time/date. I would write the following in SQL:

 

SELECT

SUM(a.Total) AS TotalSales

FROM SalesTable a

WHERE a.edition = "2017"

AND a.accountid NOT IN (SELECT accountid FROM SalesTable WHERE a.edition  = "2016")

 

Any idea if this is posibble to do in a Measure?

 

 

4 REPLIES 4
Super User IV
Super User IV

Re: Subquery

Perhaps create a calculated column first that goes something like:

 

 

BoughtLastYear = IF(ISBLANK(SUMX(FILTER(TotalSales,TotalSales[accountid]=EARLIER(TotalSales[accountid])&&TotalSales[edition]=EARLIER(TotalSales[edition]-1)),TotalSales[Total)),"No","Yes")

Should create a column with Yes if they bought last year and No if they didn't. Then you could create a Measure like:

 

 

 

MyMeasure = CALCULATE(SUM(TotalSales[Total]),FILTER(TotalSales,BoughtLastYear="Yes"))

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

ricardocamargos Established Member
Established Member

Re: Subquery

Hi @wenners68,

 

Try this code:

 

_2017 =
VAR _2016 = SELECTCOLUMNS(FILTER(SalesTable; SalesTable[EDITION] = 2016); "ACCOUNTID"; SalesTable[ACCOUNT_ID])
VAR _2017 = SELECTCOLUMNS(FILTER(SalesTable; SalesTable[EDITION] = 2017); "ACCOUNTID"; SalesTable[ACCOUNT_ID])
VAR _tbl = EXCEPT(_2017; _2016)
RETURN
SUMX(FILTER(SalesTable; SalesTable[ACCOUNT_ID] IN (_tbl)); SalesTable[TOTAL])

Community Support Team
Community Support Team

Re: Subquery

@wenners68,

 

You may take a good look at the article below.

https://www.sqlbi.com/articles/from-sql-to-dax-in-and-exists/

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
wenners68 Regular Visitor
Regular Visitor

Re: Subquery

Hi,

thanks, but that will only give you a total for the entire edition.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

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.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors