Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello there,
I have the following situation:
I have two tables:
TRANSACTIONS
id | userId | type | createdAt | processedAmount |
USERS
id | name | [Calculated] Deposits | [Calculated] Withdrawals | [Calculated] Net Deposit |
Deposits =
CALCULATE(
SUM('transactions'[processedAmount]),
FILTER (
ALL('transactions'),
'transactions'[userId] = 'users'[id]
&& ('transactions'[type] = "deposit")
)
)
Withdrawals =
CALCULATE(
SUM('transactions'[processedAmount]),
FILTER (
ALL('transactions'),
'transactions'[userId] = 'users'[id]
&& ('transactions'[type] = "withdrawal")
)
)
Net Deposit = [Deposits] - [Withdrawals]
I want to create a date filter with the createdAt value. However, I missed this binding when created calculated columns. Is it any way to use the FILTER in Dax with date placeholders so the slicer will affect such columns.
Thanks in advance.
Solved! Go to Solution.
Hi @Anonymous ,
You could use measure to achieve this goal. You could refer to my sample for details.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , Not very clear. You can join created at (Without time stamp), with a date table, and use that in the filter ?
I don't think so.
Actually, I have two calculated columns: Deposits and Withdrawals.
I created the slicer contained the createdAt values from the TRANSACTION table.
For example,
I specify the date range: 1/1/20 - 1/1/21.
I need to get the Sum of all processedAmounts with type = Deposit which was created in the specified date range (the same for Withdrawals), but I'm getting all deposits.
Hi @Anonymous ,
I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data )? Then I will help you more correctly.
Please do mask sensitive data before uploading.
Thanks for your understanding and support.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Zoe,
Thanks for your reply! Here you can find the file with sample data: https://drive.google.com/file/d/1w__Dy-dWfSB9KQomgWHqKPpnrAk2vn30/view?usp=sharing
On the dashboard, you can see one slicer and two tables. Slicer works only for one table, I need for both.
User | Count |
---|---|
93 | |
85 | |
76 | |
66 | |
62 |
User | Count |
---|---|
110 | |
96 | |
95 | |
64 | |
57 |