topic Re: Unable to add condition on 2 measures. in DAX Commands and Tips

Unable to add condition on 2 measures.

Anonymous — Sun, 27 Sep 2020 18:43:50 GMT

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 1 : SUMX(Purch,(Purch[Inovice-Amt] * RELATED(Currency_Conv[Curr_Conv])))

Measure 2 : SUM('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.

Re: Unable to add condition on 2 measures.

stevedep — Sun, 27 Sep 2020 18:50:17 GMT

Can you share some sample data?

Re: Unable to add condition on 2 measures.

Anonymous — Sun, 27 Sep 2020 20:05:48 GMT

Hi stevedep ,

Thanks for the response. Below is the sample data

PID	P_Desc	Purch_group	Measure 1	Measure 2	Measure 3
901157156	Abcd	21	634.56	32	32
911147167	nesad	34	54.7	4	4
813143561	qwe	20	-10758	329	329

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.

PID	P_Desc	Purch_group	Measure 1	Measure 2	Measure 3
901157156	Abcd	21	634.56	32	32
911147167	nesad	34	54.7	4	4
813143561	qwe	20	-10758	329	0

Thanks in advance.

Re: Unable to add condition on 2 measures.

amitchandak — Mon, 28 Sep 2020 01:22:24 GMT

Anonymous ,

Try a measure like

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

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