column calculated in relation to another column

[UserSam123]

Hello everyone,

I'm coming to you because I need help with a formula.

For some time I've been trying to create a new column that would allow me to return the column

created below. calculated column with parametre

The principle is that for the same id the 1 for example if in the stat column is present the status Accepted and cancelled or Cancelled and paid return 1 otherwise return.

I've tried using an Earlier, concatenateX, but it doesn't work at all. Do you think this is possible?

[tamerj1]

@UserSam123 

I think it's the IN operator. Please try

CalculatedColumn =
VAR CurrentIDTable =
CALCULATETABLE ( VALUES ( 'Table'[Stat] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
RETURN
IF (
ISEMPTY ( EXCEPT ( { "Cancelled" }, CurrentIDTable ) ),
IF (
NOT ISEMPTY ( INTERSECT ( { "Accepted", "Paid" }, CurrentIDTable ) ),
1,
0
),
0
)

[UserSam123]

Thank you very much, it's working fine now!
I have one last request, would it be possible to make it dynamic depending on the filter you apply?

Let's suppose that by filtering my report in id 1 there's only the paid status left, so the column should return 0 and not 1.

I imagine that you have to specify in the all function which ones you want to keep.

[tamerj1]

@UserSam123 
You can use the same formula to create a measure. It should work if both [ID] and [Status] columns are placed in the same table visual along with the measure.

Measure =
VAR CurrentIDTable =
    CALCULATETABLE ( VALUES ( 'Table'[Stat] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
RETURN
    IF (
        ISEMPTY ( EXCEPT ( { "Cancelled" }, CurrentIDTable ) ),
        IF (
            NOT ISEMPTY ( INTERSECT ( { "Accepted", "Paid" }, CurrentIDTable ) ),
            1,
            0
        ),
        0
    )

[UserSam123]

The two columns are just in two different tables 😕 Is this the only way?

[tamerj1]

@UserSam123 
What are the tables and what relationships are there between them?

[UserSam123]

It's in table 1 that I create the calculated column, which has to be dynamic. And it's in table 2 that I'm going to look for the id field 

var CurrentIDTable = CALCULATETABLE(VALUES(GESTION[Statut dossier]), ALLEXCEPT(GESTION, DOSSIER[Identifiant dossier]))

[tamerj1]

@UserSam123 

Calculated columns cannot be dynamic as they do not interact with the filter context. This has to be a measure. Try it as a measure with

VAR CurrentIDTable = CALCULATETABLE ( VALUES ( GESTION[Statut dossier] ), ALLSELECTED ( ), VALUES ( DOSSIER[Identifiant dossier] ) ) 

[UserSam123]

Thanks for your feedback;

The measurement itself works fine. But when I connect to the cube from power bi and display the measurement in a table, the lines are doubled.

In the first image it's the lines that I need to have and the second is the lines that are generated by placing the measurement in them.

The second table shows me all the rows without taking into account the filters I've placed in the filter pane.

[UserSam123]

Hello,

Do you know if this is possible or impossible?

[tamerj1 · Accepted solution]

@UserSam123 

Apologies, somehow I missed your reply on this. Please try

Measur =
VAR CurrentIDTable =
CALCULATETABLE (
VALUES ( GESTION[Statut dossier] ),
ALLSELECTED (),
VALUES ( DOSSIER[Identifiant dossier] )
)
RETURN
IF (
NOT ISEMPTY ( GESTION ) && ISEMPTY ( EXCEPT ( { "Cancelled" }, CurrentIDTable ) ),
IF (
NOT ISEMPTY ( INTERSECT ( { "Accepted", "Paid" }, CurrentIDTable ) ),
1,
0
),
0
)

[UserSam123]

Hello,

I'm coming back to you because I have a tiny inconsistency.

Here we can see that I have two files. But neither of them has a paid/cancelled or accepted/cancelled status. So the StatusVar at the bottom should be null and not 1. Am I missing something in the measurement?

[UserSam123]

Thanks ! 

[tamerj1]

Hi @UserSam123 

please try

CalculatedColumn =
VAR CurrentIDTable =
CALCULATETABLE ( VALUES ( 'Table'[Stat] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) )
RETURN
IF (
"Cancelled" IN CurrentIDTable,
IF (
NOT ISEMPTY ( INTERSECT ( { "Accepted", "Paid" }, CurrentIDTable ) ),
1,
0
),
0
)

[UserSam123]

Hello;

Thank you for your feedback. I tested it on a pbix file and it works fine.

The aim is to do it from an azure analysis services cube. When I put in the code I get this error message: Failed to save changes on the server. Error returned:

"An unexpected error has occurred (file "xldc.cpp", line 3861, function "XLAggImpl_Set<11,1>: : Accumulate").

Microsoft.AnalysisServices.BackEnd.DataModelingSandboxTabular.ExecuteEngineCodeInBackground(OperationType type, Boolean cancellable, AMOCode code, Boolean raiseEvents)
à Microsoft.AnalysisServices.BackEnd.DataModelingSandboxTabular.DoExecuteEngineCode(OperationType type, OperationCancellability cancellable, AMOCode code, Boolean raiseEvents)
à Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.ExecuteEngineCode(OperationType type, OperationCancellability cancellable, AMOCode code, Boolean raiseEvents)
à Microsoft.AnalysisServices.BackEnd.SandboxTransaction.CommitInternal(Boolean finalCommit)
----------------------------
à Microsoft.AnalysisServices.BackEnd.SandboxTransaction.CommitInternal(Boolean finalCommit)
à Microsoft.AnalysisServices.Common.SandboxEditor.ChangeFormula(TableWidgetPanel currentTable, IList`1 colIndices, IList`1 names, IList`1 expressions, Boolean doFormulaBarCommit, IList`1 displayIndices)

Would you like to know why?