Forum Discussion

PNicholsonRS's avatar
PNicholsonRS
New Member
3 years ago
Solved

Conditional calculated column issue

Hi all,   I have an issue where I need to create a new calculated column from the below data that will show when an item has been billed (i.e. Billed Amt is not blank) and collected. The problem I ...
Calculated columns
  • ERD's avatar
    ERD
    3 years ago

    Hi PNicholsonRS ,

     

    YourColumn =
SWITCH (
    TRUE (),
    NOT ( ISBLANK ( Table[Billed Amt] ) ) && Table[Collected Amt] = 0
        && ( Table[WUWD Amt] = Table[Billed Amt] || Table[Written Off] > 0 ), "Yes",
    NOT ( ISBLANK ( Table[Billed Amt] ) ) && Table[Collected Amt] > 0, "Yes",
    "No"
)

     

MorrisBI's avatar
MorrisBI
Frequent Visitor
3 years ago

Hi PNicholsonRS 

 

Here is a solution, you need a custom colum, there is a conditional colum but is limited to a fix parameters. So, once you click on custom colum you could insert the next formula or modify it as you wish.

 

I used just the billed and collecet logic, without the WUWD but is the same.

 

The steps:

1.- Set your screen to the Data panel.

2.- Right click on the table that you are trying to add your column and then select "Edit Query" (Should be in the name of the table, not the fields, otherway you won't see the option "Edit Query")

 

 

3.- Once you are on the Power Query screen, clic on Add Column.

 

4.- Then, enter the next formula:

Here is the formula in text:

if [Billed] > 0 and [Collected] > 0
then [Billed]
else "No billed/collected"

 

 

5.- Finally, the results:

 

 

Hope this could solve your issue, if so don't hesitate to mark as solved if not please reply with your comments.

 

Have a nice day 🙂