Forum Discussion

UserSam123's avatar
UserSam123
Icon for Helper I rankHelper I
3 years ago
Solved

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 colum...
  • tamerj1's avatar
    tamerj1
    3 years ago

    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
    )

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

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's avatar
UserSam123
Icon for Helper I rankHelper I
3 years ago

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's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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's avatar
      UserSam123
      Icon for Helper I rankHelper I
      3 years ago

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

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

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