Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |