Get specified columns based on nested if conditions

[anilkumar12]

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

[v-lid-msft]

Hi @anilkumar12 ,

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,

[anilkumar12]

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]

Hi @anilkumar12 ,

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,

[anilkumar12]

For example, Consider a table having the dimension and a measure.

that dimension is switched to either country or city. if the country slicer is selected(any value), then the dimension is switch to Country values, if the city slicer is selected(any value), then dimension  is switch to city.

[amitchandak]

Please refer to the example :

https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...