Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Switch to negative based on Multiple conditions (complex)

My data has a  calculated field for  Return of Investment = (MTD op income/Days in month)*365/MTD Net Investment) My DAX = CALCULATE ( MAX (CoName ) = "RETURN ON INVESTMENT",             DIVIDE ( ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Thanks VahidDM 

    I  tried  both options and the issue is at times i get 9999.00  and the  first condition gives 0 or vice versa am I incorrectly applying it TIA

     

    CALCULATE ( MAX ( 'consolidate AggSOPAccountLevel'[SOPLineDesc] )
    ) = "RETURN ON INVESTMENT", SWITCH(TRUE(), [Operating INCOME] < 0
    && [Net Investment] < 0, ROI*-1, [Operating INCOME] > 0 && [Net Investment] < 0, "9999.99",0),
    //IF (
    // [Operating INCOME] < 0
    // && [Net Investment] < 0,
    //ROI *-1,
    // IF ( [Operating INCOME] > 0 && [Net Investment] < 0, "9999.99", ABS ( roi ) )
    // ),
VahidDM's avatar
VahidDM
Icon for Super User rankSuper User
5 years ago

Hi Anonymous 

 

You need to create measue like this by using those variables:

 

Final ROI % = if([Net Investment]<0&&[Operating Income]<0,ABS(ROI %)*-1,if([Net Investment]<0&&[Operating Income]>0, 9999.99,ROI %))

 

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos !!

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thanks VahidDM 

    I  tried  both options and the issue is at times i get 9999.00  and the  first condition gives 0 or vice versa am I incorrectly applying it TIA

     

    CALCULATE ( MAX ( 'consolidate AggSOPAccountLevel'[SOPLineDesc] )
    ) = "RETURN ON INVESTMENT", SWITCH(TRUE(), [Operating INCOME] < 0
    && [Net Investment] < 0, ROI*-1, [Operating INCOME] > 0 && [Net Investment] < 0, "9999.99",0),
    //IF (
    // [Operating INCOME] < 0
    // && [Net Investment] < 0,
    //ROI *-1,
    // IF ( [Operating INCOME] > 0 && [Net Investment] < 0, "9999.99", ABS ( roi ) )
    // ),
    • v-kkf-msft's avatar
      v-kkf-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      Hi Anonymous ,

       

      Could you please share sample data or PBIX file without sensitive data?

       

      Best Regards,
      Winniz