Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to Solution.
Hi, @Anonymous
Based on your desription, I created data to reproduce your scenario. The pbix file is attached in the end.
AccountActivity:
Transactions:
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:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
Based on your desription, I created data to reproduce your scenario. The pbix file is attached in the end.
AccountActivity:
Transactions:
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:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsFlag1 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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHere 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:
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |