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.
Hi everyone,
I'm trying to replicate a table from excel to powerbi,
table1
a1 | 1 |
a2 | 3 |
a3 | 3 |
a4 | 5 |
a5 | 10 |
table 2
min >= | max < | sumif |
1 | 5 | 7 |
5 | 20 | 15 |
I need to calculate the 3rd column of the 2nd table based on the min and max criteria. I am still learning and could not seem to find the right solution.
Thank you!
Solved! Go to Solution.
Your month column should be of date type or something numeric. Just not text, cause than the Max is not the lastes date rather the last alphabetical letter in the beginning of the text
sumif =
VAR _last_date = MAX('Table 1'[Month])
RETURN
SUMX(
FILTER(
'Table 1',
'Table 1'[Value] >= 'Table 2'[Min >=]
&& 'Table 1'[Value] < 'Table 2'[Max <]
&& 'Table 1'[Month] = _last_date
),
'Table 1'[Value]
)
@Anonymous this is the calculated column in Table 2:
sumif =
SUMX(
FILTER(
'Table 1',
'Table 1'[Value] >= 'Table 2'[Min >=]
&& 'Table 1'[Value] < 'Table 2'[Max <]
),
'Table 1'[Value]
)
These are the names I used for Table 1:
And here for Table 2:
Thank you for this! Apologies but I have additional problem that I forgot to include.
table1
Category | Value | Month |
a1 | 1 | Jan 1 2022 |
a2 | 3 | Feb 1 2022 |
a3 | 3 | Mar 1 2022 |
a4 | 5 | Mar 1 2022 |
a5 | 10 | April 2022 |
What Dax can I use to just filter the latest date? This would also chnage the result in the table 2.
Thank you for your help!
@Anonymous
Depends on what you want to chieve: What do you want the result to be and where? In table 2? What result?
the table 2 should become since it would only sum the data for April 2022
Min >= | Max < | Sumif |
1 | 5 | 0 |
5 | 20 | 10 |
Your month column should be of date type or something numeric. Just not text, cause than the Max is not the lastes date rather the last alphabetical letter in the beginning of the text
sumif =
VAR _last_date = MAX('Table 1'[Month])
RETURN
SUMX(
FILTER(
'Table 1',
'Table 1'[Value] >= 'Table 2'[Min >=]
&& 'Table 1'[Value] < 'Table 2'[Max <]
&& 'Table 1'[Month] = _last_date
),
'Table 1'[Value]
)
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 | |
94 | |
83 | |
66 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |