cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Post Patron
Post Patron

How to do the equivalent of T-SQL's WHERE EXISTS in DAX

I have a Sales fact table and an AccountActivity fact table. Sales and AccountActicity both have relationships to a shared Account dimension. I have a measure that calculates sales

 

 

Sales = SUM('Sales'[Sales Amount])

 

 

I now want a new measure Sales2 that calculates Sales, but only for accounts that have had Flag1 >0  (in fact table AccountActivity) in the filter period. I tried

 

 

Sales = CALCULATE([Sales], 'AccountActivity'[Flag1] > 0)

 

but this gives the same values as [Sales]. How can I achieve this?

 

In SQL, I would use the WHERE EXISTS clause to limit the Sales query to only accounts that met the condition for Flag1. So I tried this measure below. I first get a distinct list of account numbers into a variable. Then I use CALCULATE passing the variable as a filter. But this does not work and gives the same number as before.

 

 

Sales2 =
VAR _ActWithFlag = SUMMARIZE(FILTER('AccountActivity','AccountActivity'[Flag1] > 0), 'AccountActivity'[AccountNo])
RETURN CALCULATE([Sales], _ActWithFlag)

 

Any suggestions?

 

1 ACCEPTED SOLUTION
Community Support
Community Support

Hi, @EylesIT 

 

Based on your desription, I created data to reproduce your scenario. The pbix file is attached in the end.

AccountActivity:

a1.png

 

Transactions:

a2.png

 

You may create a calculated table as below.

Table = 
FILTER(
    SUMMARIZE(
        Transactions,
        Transactions[AccountNo],
        Transactions[TxMonth],
        "Sum",
        SUM(Transactions[TxAmt])
    ),
    SUMX(
        FILTER(
            ALL(AccountActivity),
            [AccountNo]=EARLIER(Transactions[AccountNo])&&
            [ActivityMonth]=EARLIER(Transactions[TxMonth])
        ),
        AccountActivity[Flag1]
    )>0
)

 

Result:

a3.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

5 REPLIES 5
Community Support
Community Support

Hi, @EylesIT 

 

Based on your desription, I created data to reproduce your scenario. The pbix file is attached in the end.

AccountActivity:

a1.png

 

Transactions:

a2.png

 

You may create a calculated table as below.

Table = 
FILTER(
    SUMMARIZE(
        Transactions,
        Transactions[AccountNo],
        Transactions[TxMonth],
        "Sum",
        SUM(Transactions[TxAmt])
    ),
    SUMX(
        FILTER(
            ALL(AccountActivity),
            [AccountNo]=EARLIER(Transactions[AccountNo])&&
            [ActivityMonth]=EARLIER(Transactions[TxMonth])
        ),
        AccountActivity[Flag1]
    )>0
)

 

Result:

a3.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

Super User III
Super User III

Hi @EylesIT ,

 

Is your 'AccountActivity'[Flag1] a number or a text column?

 

Also be aware that measures are based on context so if you have any filters, slicers, columns or any other sort of information that can influence your result and instead of doind a direct filter to the table you need to do a ALL or ALLSELECTED.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog:

Power BI em Português





Flag1 is integer. The condition I'm looking for is Flag1 > 0.

Hi @EylesIT ,

 

Try the following code:

Sales2 =
VAR _ActWithFlag = SUMMARIZE(FILTER('AccountActivity','AccountActivity'[Flag1] > 0), 'AccountActivity'[AccountNo], "@Sales", [Sales])
RETURN SUMX(_ActWithFlag, [@Sales])

 

If this does not work can you please share a mockup data or sample of your PBIX file and expected result. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog:

Power BI em Português





Here is some test data

 

DROP TABLE IF EXISTS #transactions
CREATE TABLE #transactions(AccountNo VARCHAR(10), TxDate DATE, TxMonth VARCHAR(10), TxType VARCHAR(10), TxAmt NUMERIC(10,2))

insert #Transactions select 'ABC','2020-01-01','2020-01','Sale',101
insert #Transactions select 'ABC','2020-01-07','2020-01','Refund',513
insert #Transactions select 'ABC','2020-01-14','2020-01','Sale',183
insert #Transactions select 'ABCD','2020-01-02','2020-01','Sale',201
insert #Transactions select 'ABCD','2020-01-21','2020-01','Sale',724
insert #Transactions select 'ABCD','2020-01-25','2020-01','Interest',184
insert #Transactions select 'ABC','2020-02-02','2020-02','Sale',8126
insert #Transactions select 'ABC','2020-02-07','2020-02','Sale',652
insert #Transactions select 'ABC','2020-02-08','2020-02','Sale',13
insert #Transactions select 'ABCD','2020-02-15','2020-02','Interest',356
insert #Transactions select 'ABCD','2020-02-22','2020-02','Sale',124
insert #Transactions select 'ABCD','2020-02-26','2020-02','Sale',245


DROP TABLE IF EXISTS #accountactivity 
CREATE TABLE #accountactivity(AccountNo VARCHAR(10), ActivityDate DATE, ActivityMonth VARCHAR(10), Flag1 INT)
insert #accountactivity select 'ABC','2020-01-01','2020-01',0
insert #accountactivity select 'ABC','2020-01-02','2020-01',0
insert #accountactivity select 'ABC','2020-01-03','2020-01',0
insert #accountactivity select 'ABC','2020-01-04','2020-01',0
insert #accountactivity select 'ABC','2020-01-05','2020-01',0
insert #accountactivity select 'ABC','2020-01-06','2020-01',0
insert #accountactivity select 'ABC','2020-01-07','2020-01',0
insert #accountactivity select 'ABCD','2020-01-01','2020-01',0
insert #accountactivity select 'ABCD','2020-01-02','2020-01',0
insert #accountactivity select 'ABCD','2020-01-03','2020-01',0
insert #accountactivity select 'ABCD','2020-01-04','2020-01',5
insert #accountactivity select 'ABCD','2020-01-05','2020-01',0
insert #accountactivity select 'ABCD','2020-01-06','2020-01',0
insert #accountactivity select 'ABCD','2020-01-07','2020-01',0
insert #accountactivity select 'ABC','2020-02-01','2020-02',0
insert #accountactivity select 'ABC','2020-02-02','2020-02',0
insert #accountactivity select 'ABC','2020-02-03','2020-02',0
insert #accountactivity select 'ABC','2020-02-04','2020-02',1
insert #accountactivity select 'ABC','2020-02-05','2020-02',0
insert #accountactivity select 'ABC','2020-02-06','2020-02',0
insert #accountactivity select 'ABC','2020-02-07','2020-02',0
insert #accountactivity select 'ABCD','2020-02-01','2020-02',0
insert #accountactivity select 'ABCD','2020-02-02','2020-02',0
insert #accountactivity select 'ABCD','2020-02-03','2020-02',0
insert #accountactivity select 'ABCD','2020-02-04','2020-02',0
insert #accountactivity select 'ABCD','2020-02-05','2020-02',0
insert #accountactivity select 'ABCD','2020-02-06','2020-02',0
insert #accountactivity select 'ABCD','2020-02-07','2020-02',0

SELECT	t.AccountNo
		,t.TxMonth
		,SUM(t.TxAmt) AS Sales
FROM	#transactions AS t
WHERE	t.TxType = 'Sale'
AND	EXISTS(SELECT 1 FROM #accountactivity AS a WHERE a.AccountNo = t.AccountNo AND t.TxMonth = a.ActivityMonth AND a.Flag1 > 0)
GROUP BY t.AccountNo, t.TxMonth

 

This SQL gives the correct results: 

EylesIT_0-1601493043381.png

I load this test data into Power BI and create the measure and drop the values onto a table visual.

 

Sales With Flag1 GT 0 = 
VAR _AcInArr = SUMMARIZE(
        FILTER('AccountActivity', AccountActivity[Flag1] > 0)
        , 'AccountActivity'[AccountNo]
        , "@Sales", [Sales]
    )
    RETURN
        SUMX(_AcInArr, [@Sales])

 

but this returns nothing

 

EylesIT_1-1601493579381.png

 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors