How to use different calculation in one column using DAX?-CodePudding

I have a table like this:

I would like to calculate the result but some of ID column has different calculation formula. For ID less or equal to 1733 using this formula: Value/3*100 for ID greater than 1733 using this formula Value*100

I tried this way:

Result = 
IF('Data'[ID]<=1733,[Value]/23*100)
IF('Data'[ID]>1733,[Value]*100)

But it return an empty value. Anyone could help me please. Thank you so much

CodePudding user response：

This can be achieved using IF statement by following way by creating a calculated column called "Result"

Result = IF(Data[ID]<="1733",(Data[VALUE]/23)*100,Data[VALUE]*100)

Hope this helps !!

CodePudding user response：

If the [ID] is a text then you can convert it to a number with the VALUE(). When you are trying to convert number to text with quotes then you will get a wrong result for some of values. For text comparison it works like this: "11111"<"2", for number 11111 > 2. For a calculated column it not necessary to write a full column name like 'Data'[ID], you can simply write [ID], because of a row context. DAX will understand that the value and row you are working with is in a current table.

Result =
    SWITCH(
        TRUE()
        ,VALUE([ID])<=1733,[VALUE]/23*100
        ,[VALUE]*100
    )