Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |