Create a column with Conditions

[gheecalipes25]

Hi,

I would like to seek assistance on how to create a calculation for the below.

I wanted to create a calculated column with the below conditions:

Recurrent Amount = Amount * 12
if
Unique count = Yes
Recurrency = Recurrent
Status Description = Cancelled, Hold
Date Approved = Not Approved
Period = Monthly, Monthly(Per-User)

Thank you so much for the help. 🙂

[Gabriel_Walkman · Accepted solution]

Hi there. A calculated column can be created either in DAX or in Transform Data (Power Query Editor). You posted this in the power query forum, but I'd suspect this would normally made in DAX.

If you'd like to do this in DAX, quick google gives me https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-tutorial-create-calculated-columns...

If you'd like to do this in power query, it is very simple. You could create a conditional column and just plug in the requirements. https://radacad.com/conditional-column-in-power-bi-using-power-query-you-can-do-anything

[BA_Pete · Accepted solution]

Hi @gheecalipes25 ,

In Power Query M code:

if [Unique count] = "Yes"
  and [Recurrency] = "Recurrent"
  and List.Contains({"Cancelled", "Hold"}, [Status Description])
  and [Date Approved] = "Not Approved"
  and List.Contains({"Monthly", "Monthly(Per-User)"}, [Period])
then [Amount] * 12
else //your escape output, like null or 0

In DAX

_recurrentAmount =
IF(
  yourTable[Unique Count] = "Yes"
  && yourTable[Recurrency] = "Recurrent"
  && yourTable[Status Description] IN {"Cancelled", "Hold"}
  && yourTable[Date Approved] = "Not Approved"
  && yourTable[Period] IN {"Monthly", "Monthly(Per-User)"},
  yourTable[Amount] * 12,
  //your escape output, like null or 0
)

Pete

[gheecalipes25 · Accepted solution]

Thank you all.. 🙂

[BA_Pete · Accepted solution]

Hi @gheecalipes25 ,

In Power Query M code:

if [Unique count] = "Yes"
  and [Recurrency] = "Recurrent"
  and List.Contains({"Cancelled", "Hold"}, [Status Description])
  and [Date Approved] = "Not Approved"
  and List.Contains({"Monthly", "Monthly(Per-User)"}, [Period])
then [Amount] * 12
else //your escape output, like null or 0

In DAX

_recurrentAmount =
IF(
  yourTable[Unique Count] = "Yes"
  && yourTable[Recurrency] = "Recurrent"
  && yourTable[Status Description] IN {"Cancelled", "Hold"}
  && yourTable[Date Approved] = "Not Approved"
  && yourTable[Period] IN {"Monthly", "Monthly(Per-User)"},
  yourTable[Amount] * 12,
  //your escape output, like null or 0
)

Pete

[Gabriel_Walkman · Accepted solution]

Hi there. A calculated column can be created either in DAX or in Transform Data (Power Query Editor). You posted this in the power query forum, but I'd suspect this would normally made in DAX.

If you'd like to do this in DAX, quick google gives me https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-tutorial-create-calculated-columns...

If you'd like to do this in power query, it is very simple. You could create a conditional column and just plug in the requirements. https://radacad.com/conditional-column-in-power-bi-using-power-query-you-can-do-anything