Forum Discussion
DAX - Sum all values before the key
Dear Community,
This is what I am trying to solve for:
I have one table that looks like this:
| ID | Key |
| 1 | Schedule 1-2 |
| 2 | Schedule 1-4 |
The other table looks like this:
| Key | Value | |
| Schedule 1-1 | 0.1 | |
| Schedule 1-2 | 0.2 | |
| Schedule 1-3 | 0.3 | |
| Schedule 1-4 | 0.4 |
I need to create a measure or calculated column that would for the first table return:
ID | Key | Value | |
1 | Schedule 1-2 | 0.3 | |
| 2 | Schedule 1-4 | 1.0 |
I was thinking something along the lines of sumx?
What do people think?
never mind, i just re-read you problem, all you need to do is change my calculate to something like this:
calculate(sum(t2[value]), filter(all(t2), t2[key] <= t1[key]))
this assumes that earlier keys are less then later keys...
4 Replies
- parry2k
Super User
Anonymous I believe you already have a relationship between these two tables on the key column and the first table is on one side of the relationship, you can add column in 2nd table using the following expression
Value Column in Table 2 = RELATED ( Table1[Value] )I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
- andre
Memorable Member
if your tables are related by key, then you can use RELATED() function to pull the Value into your first table, if the tables are not related, then you can use LOOKUPVALUE function if the second table does not have duplicate values for KEYS or if it does, then you need to figure out how to shrink multiple matches to one value, so you can use MIN, MAX, AVG or SUM... then you can use CALCULATE function if you are trying to create a new colum and pass the current KEY in table one as a filter parameter for Table 2
something like calculate(max(t2[value]), filter(all(t2), t2[key] = t1[key]))
or something like that, i don't think that sumx is what you want give my understanding of what you want to do
- andre
never mind, i just re-read you problem, all you need to do is change my calculate to something like this:
calculate(sum(t2[value]), filter(all(t2), t2[key] <= t1[key]))
this assumes that earlier keys are less then later keys...
Hi Anonymous,
I'd like to suggest you add calculated columns to the second table to extract and store the right part of the schedule as index and group.
Then you can simply write measure formula to summary records based on current schedule key group and index.Group = PATHITEM(SUBSTITUTE(REPLACE([Key],1,9,""),"-","|"),1) Index = PATHITEM(SUBSTITUTE(REPLACE([Key],1,9,""),"-","|"),2)
Regards,Xiaoxin Sheng
