topic Re: IF statement and for each in DAX Commands and Tips

IF statement and for each

Anonymous — Thu, 14 Jan 2021 18:19:49 GMT

Hello,

i am new using DAX, I am using a IF statement in a custom column to add some fixed values in my costs table, i am just adding the fixed values for some specific cases where i have other columns values that i applied to the IF.

Now i need to apply in this same custom column, another condition where i will need to use the date (month and year)

So using all IFs i already have and adding the values only when a line is before a monht/year or specific date.

i have a column Date and values is like this

11/11/2020 12:00:00 AM

This is my currently code for the custom column

if [MeterCategory] = "Virtual Machines" and [MeterRegion] = "Netherlands" and [MeterName] = "D2 v3/D2s v3" then "0.07"

else if [MeterCategory] = "Virtual Machines" and [MeterRegion] = "Netherlands" and [MeterName] = "D4 v3/D4s v3" then "0.15"

else if [MeterCategory] = "Virtual Machines" and [MeterRegion] = "Netherlands" and [MeterName] = "D8 v3/D8s v3" then "0.30"

else if [MeterCategory] = "Virtual Machines" and [MeterRegion] = "Netherlands" and [MeterName] = "D16 v3/D16s v3" then "0.59"

else if [MeterCategory] = "Virtual Machines" and [MeterRegion] = "Netherlands" and [MeterName] = "E2 v3/E2s v3" then "0.09"

else if [MeterCategory] = "Virtual Machines" and [MeterRegion] = "Netherlands" and [MeterName] = "E4 v3/E4s v3" then "0.19"

else if [MeterCategory] = "Virtual Machines" and [MeterRegion] = "Netherlands" and [MeterName] = "E8 v3/E8s v3" then "0.38"

else if [MeterCategory] = "Virtual Machines" and [MeterRegion] = "Netherlands" and [MeterName] = "E16 v3/E16s v3" then "0.76"

else [Cost]

Can you help me to do this?

Re: IF statement and for each

gabrielreversi — Fri, 15 Jan 2021 01:26:01 GMT

Hello Anonymous

could you batter exemplify what you would like to do?

Re: IF statement and for each

amitchandak — Fri, 15 Jan 2021 02:23:39 GMT

Anonymous , No very Clear. You can use Date.ToText to get year month and then use that in your formula

https://docs.microsoft.com/en-us/powerquery-m/date-totext

Re: IF statement and for each

Anonymous — Fri, 15 Jan 2021 20:09:29 GMT

Hi, Gabriel, sure.

My current IF formula is adding a fixed price for lines where I have specific values for MeterCategory, MeterRegion and MeterName and it is working fine. now I need to apply the same logic, only when the Month values is equal or before November 2020. My target is to add another custom column where I will do another IF formula to add other fixed prices for lines after November 2020, so December onwards, so I need have it in order to show some fixed values according my IF formulas, before November and another after december.

I would like to do it, using the same custom column code i posted in my first post, but adding the Month condition.

Re: IF statement and for each

Anonymous — Fri, 15 Jan 2021 20:12:37 GMT

Hello, thanks for you answer.

Could you help me with more details,

your suggestion is to transform my date column into two new columns with the Month and Year information, (text), I could add this column as a condition to my previous formula?

There is a way to do it in a single line, instead of add the Month and Year condition for all lines in my previous formula?

Re: IF statement and for each

Anonymous — Thu, 21 Jan 2021 23:26:55 GMT

gabrielreversi could you help me?