Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Unable to add condition on 2 measures.

Hi Team,

 

I have a scenarios where I need to create 2 measures with some logic and basedon the 2 measure i need to create a new column or measure based on a condition. 

 

2 measures Created 

 Measure 1SUMX(Purch,(Purch[Inovice-Amt] * RELATED(Currency_Conv[Curr_Conv])))

 
Measure 2SUM('Purch'[Invoice_Qty])
 
I need to create a measure/column based on the 2 measures defined above. 
 
Measure 3 :  If(Measure 1 < 0 and Measure 2 > 0 ,0 , Measure 2)  
 
I am able to create the measure but unable to get the desired output for which my My data in table is mismatching. 
 
For few Id's I am getting multiple rows which it is being summarized and giving correct values to Measure 1 and Measure 2. But for Measure 3 it should show as 0 but its showing my measure 2 value as it falling in else statement. I knew something is missing in measure 3 calculation. I would need it output of measure 3 as 0 not measure 2. 
 
I mean how do I write in measure 3 that it should check summarized value for both Measure 1 and Measure 2 based on that it should calculate measure 3 not row by row.
 
Please guide me how to implement Measure 3 .
 
Highly appreciate for the help.
 
Thanks in advance.
3 REPLIES 3
stevedep
Memorable Member
Memorable Member

Anonymous
Not applicable

Hi @stevedep ,

 

Thanks for the response. Below is the sample data

 

PIDP_DescPurch_groupMeasure 1Measure 2 Measure 3
901157156Abcd21634.563232
911147167nesad3454.744
813143561qwe20-10758329329

 

But I need to get as below  where i am unable to add condition in Measure 3 to get as below output. For measure 3 third row I need to get 0 but getting 329. 

PIDP_DescPurch_groupMeasure 1Measure 2 Measure 3
901157156Abcd21634.563232
911147167nesad3454.744
813143561qwe20-107583290

 

Thanks in advance.

@Anonymous ,

Try a measure like

 

Sumx(values(Table[PID]), If([Measure 1] < 0 && [Measure ] > 0 ,0 , [Measure 2]))

or

Sumx(Summarize(Table[PID],"_1", If([Measure 1] < 0 && [Measure ] > 0 ,0 , [Measure 2])),[_1])

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors