The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
Hello,
I'm learning DAX and want to do something like the SUMIFS on excel. Basically i want to create another column that calculates the VALUE When the ID = REF. Like this:
ID | VALUE | REF | NEW COLUMN |
123 | 0 | 0 | |
124 | 10 | 123 | 0 |
125 | 20 | 0 | |
126 | 30 | 123 | 0 |
127 | 40 | 0 | |
128 | 50 | 124 | 10 |
129 | 60 | 0 | |
130 | 70 | 124 | 10 |
131 | 80 | 0 | |
132 | 90 | 125 | 20 |
133 | 100 | 0 | |
134 | 110 | 125 | 20 |
135 | 120 | 0 |
Thanks in advance.
Solved! Go to Solution.
Hi,
Try this calculated column formula
=IF(ISBLANK(CALCULATE(SUM(Data[VALUE]),FILTER(Data,Data[ID]=EARLIER(Data[REF])))),0,CALCULATE(SUM(Data[VALUE]),FILTER(Data,Data[ID]=EARLIER(Data[REF]))))
Hope this helps.
Hi,
Try this calculated column formula
=IF(ISBLANK(CALCULATE(SUM(Data[VALUE]),FILTER(Data,Data[ID]=EARLIER(Data[REF])))),0,CALCULATE(SUM(Data[VALUE]),FILTER(Data,Data[ID]=EARLIER(Data[REF]))))
Hope this helps.
You want to use EARLIER. See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
Thanks for the Answer Greg. i'm trying to find the right sintax to make it work, because i`m getting sintax error
Hi @Anonymous,
What about using Lookup value?
You could create a calculated column with the formula below.
Column = IF(LOOKUPVALUE('Table'[VALUE],'Table'[ID],'Table'[REF])=0,0,LOOKUPVALUE('Table'[VALUE],'Table'[ID],'Table'[REF]))
Here is the output.
If you still need help, you could show your formula and the error message so that we could help further on it.
Best Regards,
Cherry
@v-piga-msft, thanks for the reply. it worked only with a single value, but when i add more equal ids, it gives me the "multiple results"message.
@Ashish_Mathur Thanks! it worked.
You are welcome.
Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.
User | Count |
---|---|
158 | |
109 | |
96 | |
83 | |
75 |
User | Count |
---|---|
154 | |
137 | |
131 | |
81 | |
62 |