cancel
Showing results for
Search instead for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Anonymous
Not applicable

## SumIf 2 tables

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!

1 ACCEPTED SOLUTION
Community Champion

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]
)

5 REPLIES 5
Community Champion

@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:

Anonymous
Not applicable

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!

Community Champion

@Anonymous
Depends on what you want to chieve: What do you want the result to be and where? In table 2? What result?

Anonymous
Not applicable

the table 2 should become since it would only sum the data for April 2022

 Min >= Max < Sumif 1 5 0 5 20 10
Community Champion

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]
)

## Helpful resources

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors