Forum Discussion
Calculated Column's formula is based on a value in a column
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!
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.
5 Replies
- lbendlin
Super User
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.
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.
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
- 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.
