Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Get specified columns based on nested if conditions

Hello All,

 

Iam working on migration project and have requirment like this:

In a table, i want to display different columns based on different conditions in single dax. For example, if condition-A becomes true , i need to display column1, if cond-2 is true, i want to display column2, if cond-3, column3.....This is working for pie chart visual, but not for table.

 

Pls let me know solution for this.

 

For refrence purpose, iam attaching the qlik expression here, which need to be converted to dax.

 

=if(GetSelectedCount(Direct) > 0 and GetSelectedCount(VP_Director) = 0 and GetSelectedCount(Director_Manager) = 0 and GetSelectedCount(Manager) = 0
, VP_Director,

if( (GetSelectedCount(Direct) > 0 and GetSelectedCount(VP_Director) > 0 and GetSelectedCount(Director_Manager) = 0 and GetSelectedCount(Manager) = 0)
or (GetSelectedCount(VP_Director) > 0 and GetSelectedCount(Direct) = 0 and GetSelectedCount(Director_Manager) = 0 and GetSelectedCount(Manager) = 0)
, Director_Manager,

if( (GetSelectedCount(Direct) > 0 and GetSelectedCount(VP_Director) > 0 and GetSelectedCount(Director_Manager) > 0 and GetSelectedCount(Manager) = 0)
or (GetSelectedCount(VP_Director) = 0 and GetSelectedCount(Direct) > 0 and GetSelectedCount(Director_Manager) > 0 and GetSelectedCount(Manager) = 0)
or (GetSelectedCount(VP_Director) > 0 and GetSelectedCount(Direct) = 0 and GetSelectedCount(Director_Manager) > 0 and GetSelectedCount(Manager) = 0)
, Manager,


if( (GetSelectedCount(Direct) > 0 and GetSelectedCount(VP_Director) > 0 and GetSelectedCount(Director_Manager) > 0 and GetSelectedCount(Manager) > 0)
or (GetSelectedCount(VP_Director) = 0 and GetSelectedCount(Direct) > 0 and GetSelectedCount(Director_Manager) > 0 and GetSelectedCount(Manager) > 0)
or (GetSelectedCount(VP_Director) > 0 and GetSelectedCount(Direct) = 0 and GetSelectedCount(Director_Manager) > 0 and GetSelectedCount(Manager) > 0)
or (GetSelectedCount(VP_Director) = 0 and GetSelectedCount(Direct) = 0 and GetSelectedCount(Director_Manager) = 0 and GetSelectedCount(Manager) > 0)
, SUBAREA_OWNER

))))

 

 

 

TIA

Need Help

5 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    we can try to convert it to the following  dax to meet your requirement:

     

    Measure =
IF (
    ISINSCOPE ( 'Table'[Direct] ) > 0
        && ISINSCOPE ( 'Table'[VP_Director] ) = 0
        && ISINSCOPE ( 'Table'[Director_Manager] ) = 0
        && ISINSCOPE ( 'Table'[Manager] ) = 0,
    MAX ( 'Table'[VP_Director] ),
    IF (
        (
            ISINSCOPE ( 'Table'[Direct] ) > 0
                && ISINSCOPE ( 'Table'[VP_Director] ) > 0
                && ISINSCOPE ( 'Table'[Director_Manager] ) = 0
                && ISINSCOPE ( 'Table'[Manager] ) = 0
        )
            || (
                ISINSCOPE ( 'Table'[VP_Director] ) > 0
                    && ISINSCOPE ( 'Table'[Direct] ) = 0
                    && ISINSCOPE ( 'Table'[Director_Manager] ) = 0
                    && ISINSCOPE ( 'Table'[Manager] ) = 0
            ),
        MAX ( 'Table'[Director_Manager] ),
        IF (
            (
                ISINSCOPE ( 'Table'[Direct] ) > 0
                    && ISINSCOPE ( 'Table'[VP_Director] ) > 0
                    && ISINSCOPE ( 'Table'[Director_Manager] ) > 0
                    && ISINSCOPE ( 'Table'[Manager] ) = 0
            )
                || (
                    ISINSCOPE ( 'Table'[VP_Director] ) = 0
                        && ISINSCOPE ( 'Table'[Direct] ) > 0
                        && ISINSCOPE ( 'Table'[Director_Manager] ) > 0
                        && ISINSCOPE ( 'Table'[Manager] ) = 0
                )
                || (
                    ISINSCOPE ( 'Table'[VP_Director] ) > 0
                        && ISINSCOPE ( 'Table'[Direct] ) = 0
                        && ISINSCOPE ( 'Table'[Director_Manager] ) > 0
                        && ISINSCOPE ( 'Table'[Manager] ) = 0
                ),
            MAX ( 'Table'[Manager] ),
            IF (
                (
                    ISINSCOPE ( 'Table'[Direct] ) > 0
                        && ISINSCOPE ( 'Table'[VP_Director] ) > 0
                        && ISINSCOPE ( 'Table'[Director_Manager] ) > 0
                        && ISINSCOPE ( 'Table'[Manager] ) > 0
                )
                    || (
                        ISINSCOPE ( 'Table'[VP_Director] ) = 0
                            && ISINSCOPE ( 'Table'[Direct] ) > 0
                            && ISINSCOPE ( 'Table'[Director_Manager] ) > 0
                            && ISINSCOPE ( 'Table'[Manager] ) > 0
                    )
                    || (
                        ISINSCOPE ( 'Table'[VP_Director] ) > 0
                            && ISINSCOPE ( 'Table'[Direct] ) = 0
                            && ISINSCOPE ( 'Table'[Director_Manager] ) > 0
                            && ISINSCOPE ( 'Table'[Manager] ) > 0
                    )
                    || (
                        ISINSCOPE ( 'Table'[VP_Director] ) = 0
                            && ISINSCOPE ( 'Table'[Direct] ) = 0
                            && ISINSCOPE ( 'Table'[Director_Manager] ) = 0
                            && ISINSCOPE ( 'Table'[Manager] ) > 0
                    ),
                MAX ( 'Table'[SUBAREA_OWNER] )
            )
        )
    )
)

     


    If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.


    Best regards,

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      is returning error like:
      Dax operations do not support comparing type  true/false with type of integer.

      Here, isinscope function returns either true or false.

      • v-lid-msft's avatar
        v-lid-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous ,

         

        Sorry for our mistake in formula, please try to use the following:

         

        Measure =
IF (
    ISINSCOPE ( 'Table'[Direct] )
        && NOT (
            ISINSCOPE ( 'Table'[VP_Director] )
        )
        && NOT (
            ISINSCOPE ( 'Table'[Director_Manager] )
        )
        && NOT (
            ISINSCOPE ( 'Table'[Manager] )
        ),
    MAX ( 'Table'[VP_Director] ),
    IF (
        (
            ISINSCOPE ( 'Table'[Direct] )
                && ISINSCOPE ( 'Table'[VP_Director] )
                && NOT (
                    ISINSCOPE ( 'Table'[Director_Manager] )
                )
                && NOT (
                    ISINSCOPE ( 'Table'[Manager] )
                )
        )
            || (
                ISINSCOPE ( 'Table'[VP_Director] )
                    && NOT (
                        ISINSCOPE ( 'Table'[Direct] )
                    )
                    && NOT (
                        ISINSCOPE ( 'Table'[Director_Manager] )
                    )
                    && NOT (
                        ISINSCOPE ( 'Table'[Manager] )
                    )
            ),
        MAX ( 'Table'[Director_Manager] ),
        IF (
            (
                ISINSCOPE ( 'Table'[Direct] )
                    && ISINSCOPE ( 'Table'[VP_Director] )
                    && ISINSCOPE ( 'Table'[Director_Manager] )
                    && NOT (
                        ISINSCOPE ( 'Table'[Manager] )
                    )
            )
                || (
                    NOT (
                        ISINSCOPE ( 'Table'[VP_Director] )
                    )
                        && ISINSCOPE ( 'Table'[Direct] )
                        && ISINSCOPE ( 'Table'[Director_Manager] )
                        && NOT (
                            ISINSCOPE ( 'Table'[Manager] )
                        )
                )
                || (
                    ISINSCOPE ( 'Table'[VP_Director] )
                        && NOT (
                            ISINSCOPE ( 'Table'[Direct] )
                        )
                        && ISINSCOPE ( 'Table'[Director_Manager] )
                        && NOT (
                            ISINSCOPE ( 'Table'[Manager] )
                        )
                ),
            MAX ( 'Table'[Manager] ),
            IF (
                (
                    ISINSCOPE ( 'Table'[Direct] )
                        && ISINSCOPE ( 'Table'[VP_Director] )
                        && ISINSCOPE ( 'Table'[Director_Manager] )
                        && ISINSCOPE ( 'Table'[Manager] )
                )
                    || (
                        NOT (
                            ISINSCOPE ( 'Table'[VP_Director] )
                        )
                            && ISINSCOPE ( 'Table'[Direct] )
                            && ISINSCOPE ( 'Table'[Director_Manager] )
                            && ISINSCOPE ( 'Table'[Manager] )
                    )
                    || (
                        ISINSCOPE ( 'Table'[VP_Director] )
                            && NOT (
                                ISINSCOPE ( 'Table'[Direct] )
                            )
                            && ISINSCOPE ( 'Table'[Director_Manager] )
                            && ISINSCOPE ( 'Table'[Manager] )
                    )
                    || (
                        NOT (
                            ISINSCOPE ( 'Table'[VP_Director] )
                        )
                            && NOT (
                                ISINSCOPE ( 'Table'[Direct] )
                            )
                            && NOT (
                                ISINSCOPE ( 'Table'[Director_Manager] )
                            )
                            && ISINSCOPE ( 'Table'[Manager] )
                    ),
                MAX ( 'Table'[SUBAREA_OWNER] )
            )
        )
    )
)

         

        Could you please describe the expected result simply if it does not contain any confidential information, do you want to create a column in table or use a measure in visual?


        Best regards,