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
Anonymous
Not applicable

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
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

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
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

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.

 

MFelix
Super User
Super User

Hi @Anonymous ,

 

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



Anonymous
Not applicable

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

Hi @Anonymous ,

 

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



Anonymous
Not applicable

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