Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have a measure which adds values from related tables Table A and Table B.
Currently it just reads Measure1 = sum(TableA[value]) + sum(TableB[value])
This measure works great.
Now, how do I create a measure for just where "Measure 1 > 100"? I have played around with sumx and calculate to no avail. I have changed Measure1 to use "sumx" function instead of sum.
Please help! Thank you.
Solved! Go to Solution.
Hi,
Try this measure
=LargeDepositors = CALCULATE([TotalDeposits],FILTER(VALUES(ACCT[ACCT]),[TotalDeposits]>100000))
Hope this helps.
Hi,
It will be helpful if you can show the data and the expected result.
Imagine you have 2 kinds of depositsin a financial institution: Shares and Certificates. They each are in different tables, both tables containing account numbers.
One measure is: Total_Deposits = sum(CERT[CECBAL])+sum(SHAR[SHCBAL])
What I need/would like is to create a "Large_Deposits" measure, that is: "Total Deposits > 100000"
Hi,
Try this
=CALCULATE([Total_Deposits],FILTER([Total_deposits],[Total_deposits]>100000))
Hope this helps.
I get this error message: "the FILTER function expects a table expression for argument '1', but a string or numeric expression was used."
Hi,
Sorry, try this
=CALCULATE([Total_Deposits],FILTER(Data,[Total_deposits]>100000))
Yes, this is the formula but the "data" part is the essence of my question I think... Since "Total_Deposits" comes from two separate tables, how do I "filter" BOTH tables and get the results from BOTH. Something with "relatedtable" maybe?
TotalDeposits = sum(SHAR[Share_Balance]) + sum(CERT[Share_Balance]). So when I FILTER, how do I still get the results from BOTH tables?
Hi,
I dont think that should matter. Do you get an incorrect result?
That solved the problem of pulling those together! Even though I just used one table in the "filter" it seemed to work. (Hooray for relationships!)
So here's what I did: LargeDeposits = calculate([Total_Deposits],filter(SHAR,[Total_Deposits]>100000))
Okay, one other small problem: The same account can have multiple rows in the SHAR table. (For example, you could have a Money Market and a Checking). For "large deposits," it is filtering on a row-by-row basis, so if you have a $150,000 Money Market that is being included, but it would exclude your $30,000 checking suffix.
How can I tweak my filter to include ALL SHARes for that account if the Total Deposits metric is >100,000?
I can change the filter to be my "CERT" table, but then it would exclude Certificates that are less than $100,000.
Ahh!! It's so close! BTW, thank you for your continued help on this!! You get litle more than "good karma" so thank you!
You are welcome. To assist you, i will need your data. Show me your data and your expected result.
Will this let you download my sample data file?
https://drive.google.com/open?id=1ZqHtJbQtcIR-tCy9N4OSm_mWn64wAn9J
I added a visual (table) and a text box with some additional explanation. A picture is worth a thousand words!
Hi,
Try this measure
=LargeDepositors = CALCULATE([TotalDeposits],FILTER(VALUES(ACCT[ACCT]),[TotalDeposits]>100000))
Hope this helps.
Thank you thank you!! Of course filtering the PARENT table was the way to go, and using that values function is genius.
You are welcome.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
81 | |
64 | |
49 |
User | Count |
---|---|
123 | |
109 | |
88 | |
68 | |
67 |