Forum Discussion
column calculated in relation to another column
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?
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
)
14 Replies
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
)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?
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
)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.
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 )
