Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Experts,
I am getting this error, while am creating calculated coloumn, How to rectify it. Please help.
Solved! Go to Solution.
@Anonymous
I have added the Max Buiness Date logic for each customer and modified the code. please try:
BALFUNDS =
VAR _BALFUNDS = Holdings[market_value_in_usd]
VAR __MaxBusDate = CALCULATE( MAX( Holdings[BusinessDate]), ALLEXCEPT( Holdings , Holdings[Customer_No] ) )
RETURN
IF (
Holdings[BALFUNDS_CC] = "Funds" && Holdings[BusinessDate] = __MaxBusDate,
SWITCH (
TRUE (),
_BALFUNDS >= 0
&& _BALFUNDS < 2000000, "<6m",
_BALFUNDS >= 2000000
&& _BALFUNDS < 5000000, "4m-<4m",
_BALFUNDS >= 5000000
&& _BALFUNDS < 10000000, "4m-<8m",
_BALFUNDS >= 10000000
&& _BALFUNDS < 20000000, "10m-<20m",
_BALFUNDS >= 20000000, "No "
)
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
Remove the filter from the Holdings table.
Try your column this way:
BALFUNDS =
CALCULATE (
YOUR CODE GOES HERE...,
REMOVEFILTERS ( 'HOLDINGS')
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @Fowmy
Can you Type here where should make changes?
This is my DAX Column
BALFUNDS =
var _BALFUNDS = if( Holdings[BALFUNDS_CC] = "Funds" && Holdings[BusinessDate] = [Max_BusinessDate],Holdings[market_value_in_usd],BLANK())
return IF (_BALFUNDS>=0 && _BALFUNDS<2000000, "<6m",
IF(_BALFUNDS>=2000000 && _BALFUNDS<5000000, "4m-<4m",
IF(_BALFUNDS>=5000000 && _BALFUNDS<10000000, "4m-<8m",
IF(_BALFUNDS>=10000000 && _BALFUNDS<20000000, "10m-<20m",
IF(_BALFUNDS>=20000000,"No ")))))
@Anonymous
I am not sure why you need the measure to get the Max business date. you are creating a column and the measure will always give you the last date in the table as there is no filter context during column creation.
Try the following code:
BALFUNDS =
VAR _BALFUNDS = Holdings[market_value_in_usd]
RETURN
IF (
Holdings[BALFUNDS_CC] = "Funds",
SWITCH (
TRUE (),
_BALFUNDS >= 0
&& _BALFUNDS < 2000000, "<6m",
_BALFUNDS >= 2000000
&& _BALFUNDS < 5000000, "4m-<4m",
_BALFUNDS >= 5000000
&& _BALFUNDS < 10000000, "4m-<8m",
_BALFUNDS >= 10000000
&& _BALFUNDS < 20000000, "10m-<20m",
_BALFUNDS >= 20000000, "No "
)
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi Fowmy,
Thank you for your quick response. Much appreciated! My Requirement is to create a calculated column for Bal_Funds.
Bal_Funds : If Bal_FundsCC= “Funds” than Sum of market_value_in_usd per Customer_No for latest BusinessDate.
BuisnessDate, market_value_in_usd, Bal_FundsCC are from Same table "Holdings".
Thats why I am creating the max business date. In this case, Can I use your code or any changes to be made in your code.
Please suggest.
Many Thanks!
@Anonymous
I have added the Max Buiness Date logic for each customer and modified the code. please try:
BALFUNDS =
VAR _BALFUNDS = Holdings[market_value_in_usd]
VAR __MaxBusDate = CALCULATE( MAX( Holdings[BusinessDate]), ALLEXCEPT( Holdings , Holdings[Customer_No] ) )
RETURN
IF (
Holdings[BALFUNDS_CC] = "Funds" && Holdings[BusinessDate] = __MaxBusDate,
SWITCH (
TRUE (),
_BALFUNDS >= 0
&& _BALFUNDS < 2000000, "<6m",
_BALFUNDS >= 2000000
&& _BALFUNDS < 5000000, "4m-<4m",
_BALFUNDS >= 5000000
&& _BALFUNDS < 10000000, "4m-<8m",
_BALFUNDS >= 10000000
&& _BALFUNDS < 20000000, "10m-<20m",
_BALFUNDS >= 20000000, "No "
)
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thank you very much for your timely help. Really Appreciated!
Please see this article. Also, what columns are used in the measure referenced in your expression (Max_BusinessDate)?
Avoiding circular dependency errors in DAX - SQLBI
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |