Assign different value based on other column's value

[maryfree86]

Hello,

I would like to make a new column based on the condition of my other columns. Example as below. Column A to D is my dataset and column E will be the column that I would like to create in powerbi table. The formula will be =if(and(B1=B2, C1=C2,D1=D2),E2,"AAA"&ROW()). I am not sure how to write Dax function to have this column create in powerbi table.

[v-csrikanth]

Hi @maryfree86 

We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @maryfree86 

It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @maryfree86 
Sorry for the late response.
Thank you for being part of the Microsoft Fabric Community.

The error you're getting "A circular dependency was detected" — is due to the calculated column referencing itself ([Assign Value]) inside its own logic. DAX calculated columns cannot look forward or access their own value while being evaluated row by row.

You want to check for previous rows with the same ID, Date, and Frequency, and reuse the assign value from there if matched, otherwise generate a new value "AAA" & Index.

To avoid the circular reference, we must calculate this without ever referring to [Assign Value] itself.

Please check the updated Dax as follows:
-----------------------------------------------------------
Assign Value =

VAR CurrentIndex = 'Table'[Index]

VAR CurrentID = 'Table'[ID]

VAR CurrentDate = 'Table'[Date]

VAR CurrentFreq = 'Table'[Freq]

VAR MatchedIndex =

CALCULATE(

    MIN('Table'[Index]),

    FILTER(

        'Table',

        'Table'[Index] < CurrentIndex &&

        'Table'[ID] = CurrentID &&

        'Table'[Date] = CurrentDate &&

        'Table'[Freq] = CurrentFreq

    )

)

RETURN

IF(

    ISBLANK(MatchedIndex),

    "AAA" & CurrentIndex,

    "AAA" & MatchedIndex

)
-----------------------------------------------------------

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @maryfree86 

I wanted to check if you had a chance to try the suggested solution.
Please let us know if you need any further assistance.

Looking forward to your update!

Thank you.

[v-csrikanth]

Hi @maryfree86 
Sorry for the late response.
Yes, when creating a calculated column in Power BI using DAX, you cannot refer to the same column (Assign value) that is being created within its own formula.
Instead, you need an approach that iterates through previous rows while ensuring proper assignment.
Please use updated DAX formula that resolve your issue:

*********************************************************************

Assign Value =
VAR CurrentIndex = 'Table'[Index]
VAR CurrentID = 'Table'[ID]
VAR CurrentDate = 'Table'[Date]
VAR CurrentFreq = 'Table'[Freq]

VAR PrevAssignValue =
CALCULATE(
MAX('Table'[Assign Value]),
FILTER(
'Table',
'Table'[Index] < CurrentIndex &&
'Table'[ID] = CurrentID &&
'Table'[Date] = CurrentDate &&
'Table'[Freq] = CurrentFreq
)
)

RETURN
IF(
ISBLANK(PrevAssignValue),
"AAA" & CurrentIndex,
PrevAssignValue
)
*********************************************************************

If the above information helps you, please give us a Kudos and marked the reply Accept as a Solution.

Thanks,
Cheri Srikanth

[maryfree86]

Hi @v-csrikanth ,

I am still having error "A circular dependency was detected: 'Table'[Assign Value]."

[Shravan133]

Try this:

Column E =
VAR CurrentRow = 'Table'[Row ID] -- Replace Row ID with a unique identifier for each row
VAR PrevRow =
CALCULATE(
MAX('Table'[Row ID]),
FILTER(
'Table',
'Table'[Row ID] < CurrentRow
)
)
VAR SameRow =
IF (
'Table'[Column B] = CALCULATE(MAX('Table'[Column B]), FILTER('Table', 'Table'[Row ID] = PrevRow)) &&
'Table'[Column C] = CALCULATE(MAX('Table'[Column C]), FILTER('Table', 'Table'[Row ID] = PrevRow)) &&
'Table'[Column D] = CALCULATE(MAX('Table'[Column D]), FILTER('Table', 'Table'[Row ID] = PrevRow)),
TRUE,
FALSE
)
RETURN
IF(SameRow,
'Table'[Column E],
"AAA" & CurrentRow
)

or 

Column E =
VAR CurrentB = 'Table'[Column B]
VAR CurrentC = 'Table'[Column C]
VAR CurrentD = 'Table'[Column D]
VAR NextRow =
CALCULATE(
MAX('Table'[Column E]),
FILTER(
'Table',
'Table'[Column B] = CurrentB &&
'Table'[Column C] = CurrentC &&
'Table'[Column D] = CurrentD &&
'Table'[Index] < EARLIER('Table'[Index]) -- Assuming 'Index' is a sequential column
)
)
RETURN
IF(ISBLANK(NextRow), "AAA" & EARLIER('Table'[Index]), NextRow)

[maryfree86]

Hi @Shravan133 ,

I tried the formular that you provided above for both, it seems like none of them working. Since I put 'Table'[Column E](replace with my table and comlun name), it gives error. Not sure if when I created new column, I can refer to the new column name in the formula