Calculated Column's formula is based on a value in a column

[Anonymous]

Sorry if the subject is really vague, but what I just want to know if this is possible in Power BI. Let's say I have this table:

Country	Created Date	Assigned Date	Completed Date	Closed Date	Date Difference
Philippines	XXX	XXX	XXX	XXX
USA	XXX	XXX	XXX	XXX
Thailand	XXX	XXX	XXX	XXX

Then the column "Date Difference" above would be a calculated column in which the calculation varies and I will have another table to indicated what formula and columns are involved in the calculation, let's say another table like this:

Country	Date Difference Calculation
Phillippines	Closed Date - Created Date
USA	Completed Date - Created Date
Thailand	Closed Date - Assigned Date

You may think that I could have this be type out as a Dax formula as they just only involved 3 countries with a simple If and else statement but my problem is that it involves hundred of countries and typing it out is near to impossible. Would you know how to convert a calculation based on a column values to be a calculated column in Power BI?

Thank you very much and have a great day!

[v-rzhou-msft]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[Anonymous]

I get the solution but I am having 2nd thoughts on that one as I am expecting that there would be hundreds of different calculations and doing that method will require me to input all the calculations. I was thinking more of using the Expression.evaluate()

[lbendlin]

Yes, go do that.  Have a table with country, date A and date B  column names, and then use Expression.Evaluate() to run the dynamic formula.  Column names need to be encapsulated in brackets  (eg [Created Date])  which makes your original format less usable.

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

You can't use the formula in columns, but you can use switch function like  lbendlin 's reply to calculate date difference.

In addition to  lbendlin 's reply, I build a table to have a test.

Build a calculated column:

Date Difference = 
VAR _DateDifferenceCalucation = CALCULATE(MAX('Calculation'[Date Difference Calculation]),FILTER('Calculation','Calculation'[Country]=EARLIER('Table'[Country])))
VAR _Closed_Created =
    DATEDIFF ( 'Table'[Created Date], 'Table'[Closed Date], DAY )
VAR _Closed_Assigned =
    DATEDIFF ( 'Table'[Assigned Date], 'Table'[Closed Date], DAY )
VAR _Closed_Completed =
    DATEDIFF ( 'Table'[Completed Date], 'Table'[Closed Date], DAY )
VAR _Completed_Assigned =
    DATEDIFF ( 'Table'[Assigned Date], 'Table'[Completed Date], DAY )
VAR __Completed_Created =
    DATEDIFF ( 'Table'[Created Date], 'Table'[Completed Date], DAY )
RETURN
    SWITCH (
        _DateDifferenceCalucation,
        "Closed Date - Created Date", _Closed_Created,
        "Completed Date - Created Date", __Completed_Created,
        "Closed Date - Assigned Date", _Closed_Assigned,
        "Completed Date - Assigned Date", _Completed_Assigned,
        _Closed_Completed
    )

Result:

Here I calculate the day differences between different date. You can change the Day to year, month, Hour and so on.

For more info to dax functions: Switch function  Datediff function 

You can download the pbix file from this link: Calculated Column's formula is based on a value in a column

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[lbendlin]

Yes, you can do that in DAX, sort of, but not in a column.

There is no EVALUATE() function in Power BI DAX (sadly!) so you have to resort to a dynamic measure:

Result := SWITCH([Date Difference Calculation],"Closed Date - Created Date",<formula here>,"Completed Date - Created Date",<formula here>,<alternative formula>)

Having said that there is an Expression.Evaluate() option in Power Query where you can actually do that to a column.