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.

Reply
Oleh
New Member

CALCULATE function based on criteria referring to two columns in one table

Hi All,

 

I have the following input data and need to calulte column Result using DAX.

 

MonthValueID_1ID_2Result
January              12a 0
February              14a 0
March                5b 0
April                7b 0
May                8b 0
June                1c 0
July              41b 0
August              24 a26
September              33 b61

 

In excel I would simply using SUMIFS with criteria range as column "ID_1" and criteria as column "ID_2":Capture.PNG

In DAX I am trying to make the following:

Result = if('Sample'[ID_2] = "", 0 , CALCULATE ( sum ('Sample'[Value]), FILTER ( 'Sample' , 'Sample'[ID_1] = 'Sample'[ID_2] ) ) )
 
But it does not produce result I expect.
 
Can you please advise how can I properly build DAX expression?
 
Thank you in advance,
best regards,
Oleh
1 ACCEPTED SOLUTION
nvprasad
Solution Sage
Solution Sage

Hi,

Can you try below formula 🙂

Result =
Var currntrow = abcd[ID_2]
RETURN
IF(abcd[ID_2]=BLANK(),0,CALCULATE(sum(abcd[Value]),FILTER(abcd,abcd[ID_1]=currntrow)))
 

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad

 

 

View solution in original post

2 REPLIES 2
nvprasad
Solution Sage
Solution Sage

Hi,

Can you try below formula 🙂

Result =
Var currntrow = abcd[ID_2]
RETURN
IF(abcd[ID_2]=BLANK(),0,CALCULATE(sum(abcd[Value]),FILTER(abcd,abcd[ID_1]=currntrow)))
 

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad

 

 

Big thank you! It works!

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

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

Community News

Fabric Community News unified experience

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

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

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