Forum Discussion

Tybaal's avatar
Tybaal
Icon for Helper II rankHelper II
5 years ago
Solved

Make a value as negative depending on a parameter from another table

Hi everyone,    I'd like to value from my fact table negative if a field from a dimension table has a certain value. For exemple :  If ('Dimension Type'[Type] = "A", SUM('Fact sales'[Amount] * -1...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Tybaal , something like this

    if (max('Dimension Type'[Type]) = "A", SUM('Fact sales'[Amount]) * -1, IF('Dimension Type'[Type] = "B", SUM('Fact sales'[Amount])))

    or

    sumx(values('Dimension Type'[Type]) ,if ('Dimension Type'[Type] = "A", SUM('Fact sales'[Amount]) * -1, IF('Dimension Type'[Type] = "B", SUM('Fact sales'[Amount]))))

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Tybaal , something like this

if (max('Dimension Type'[Type]) = "A", SUM('Fact sales'[Amount]) * -1, IF('Dimension Type'[Type] = "B", SUM('Fact sales'[Amount])))

or

sumx(values('Dimension Type'[Type]) ,if ('Dimension Type'[Type] = "A", SUM('Fact sales'[Amount]) * -1, IF('Dimension Type'[Type] = "B", SUM('Fact sales'[Amount]))))

Tybaal's avatar
Tybaal
Icon for Helper II rankHelper II
5 years ago

Thank you amitchandak , it works with your first proposition :

 

if (max('Dimension Type'[Type]) = "A", SUM('Fact sales'[Amount]) * -1, IF(max('Dimension Type'[Type] = "B", SUM('Fact sales'[Amount])))

 

But can you explain why it didn't work without the 'max'? (sorry i'm a beginner in DAX)