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
    )

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?

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

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