topic Re: column calculated in relation to another column in DAX Commands and Tips

column calculated in relation to another column

UserSam123 — Sun, 18 Jun 2023 15:26:52 GMT

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?

Re: column calculated in relation to another column

tamerj1 — Sun, 18 Jun 2023 15:43:10 GMT

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
)

Re: column calculated in relation to another column

UserSam123 — Sun, 18 Jun 2023 20:00:23 GMT

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?

Re: column calculated in relation to another column

tamerj1 — Mon, 19 Jun 2023 03:12:48 GMT

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
)

Re: column calculated in relation to another column

UserSam123 — Tue, 20 Jun 2023 12:42:43 GMT

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.

Re: column calculated in relation to another column

tamerj1 — Tue, 20 Jun 2023 12:53:03 GMT

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 )

Re: column calculated in relation to another column

UserSam123 — Tue, 20 Jun 2023 13:02:36 GMT

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

Re: column calculated in relation to another column

tamerj1 — Tue, 20 Jun 2023 14:03:58 GMT

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

Re: column calculated in relation to another column

UserSam123 — Tue, 20 Jun 2023 14:25:13 GMT

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]))

Re: column calculated in relation to another column

tamerj1 — Tue, 20 Jun 2023 14:58:03 GMT

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] ) )

Re: column calculated in relation to another column

UserSam123 — Wed, 21 Jun 2023 11:46:19 GMT

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.

Re: column calculated in relation to another column

UserSam123 — Sun, 25 Jun 2023 14:30:49 GMT

Hello,

Do you know if this is possible or impossible?

Re: column calculated in relation to another column

tamerj1 — Sun, 25 Jun 2023 16:33:19 GMT

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
)

Re: column calculated in relation to another column

UserSam123 — Mon, 10 Jul 2023 16:59:07 GMT

Thanks !

Re: column calculated in relation to another column

UserSam123 — Tue, 11 Jul 2023 14:50:54 GMT

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?