Slicer out of two data sources

[AndrejZitnay]

Hello all,

I was wondering if you could help me out. I have my visuals (table) where I need to drill down by two slicers which are from two different data sources.

I am hoping to have only one slicer with following 5 conditons as it is quite difficult to use two slicer with given condtions.

Is there any smart way how to achive that?

Many thanks.

Kind regards.

Andrej

[v-juanli-msft]

Hi @AndrejZitnay 

You could use filter pane to filter the "Number" in excel

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AndrejZitnay]

Hello @v-juanli-msft 

Thanks for your help.
I am aware of filters but that is not what I need.

I have table with lots of variables. This table is linked via relationships to two separate data sources Excel 1 & Excel 2 which works as slicers . At the moment I can work with my table with two separate Slicers : Category & Number

I need to create 1 slicer which will meet my conditions for Category 1-5.

Hope that make sense.
Here is picture with further explanation.

Many thanks.

Kind regards.

Andrej

[v-juanli-msft · Accepted solution]

Hi @AndrejZitnay 

Create a table by entering data, don't create any relationship for this table

category

category1

category2

category3

category4

category5

Create columns in main table

1 =
IF (
    [category] IN { "Train" }
        && FIND (
            [Number],
            FORMAT (
                CONCATENATEX ( VALUES ( 'main table'[Number] ), [Number], "," ),
                "General Number"
            ),
            1,
            0
        ) > 0,
    "category1"
)

4 =
IF (
    [category] IN { "Car", "Airplane", "Train" }
        && FIND (
            [Number],
            FORMAT (
                CONCATENATEX ( VALUES ( 'main table'[Number] ), [Number], "," ),
                "General Number"
            ),
            1,
            0
        ) > 0,
    "category4"
)

5 =
IF (
    [category] IN { "Car", "Airplane", "Train", "Boat" }
        && FIND (
            [Number],
            FORMAT (
                CONCATENATEX ( VALUES ( 'main table'[Number] ), [Number], "," ),
                "General Number"
            ),
            1,
            0
        ) > 0,
    "category5"
)

3 =
IF (
    [category] IN { "Car", "Airplane", "Train" }
        && FIND (
            [Number],
            CONCATENATEX (
                FILTER ( VALUES ( 'main table'[Number] ), NOT [Number] IN { 5649 } ),
                [Number],
                ","
            ),
            1,
            0
        ) > 0,
    "category3"
)


2 =
IF (
    [category] IN { "Car", "Airplane", "Train" }
        && FIND (
            [Number],
            CONCATENATEX (
                FILTER ( VALUES ( 'main table'[Number] ), NOT [Number] IN { 6546, 5649 } ),
                [Number],
                ","
            ),
            1,
            0
        ) > 0,
    "category2"
)

Then

Add "category" in a slicer

create a measure in main table

Measure =
SWITCH (
    SELECTEDVALUE ( 'slicer table'[category] ),
    "category1", IF ( MAX ( 'main table'[1] ) = "category1", 1 ),
    "category2", IF ( MAX ( 'main table'[2] ) = "category2", 1 ),
    "category3", IF ( MAX ( 'main table'[3] ) = "category3", 1 ),
    "category4", IF ( MAX ( 'main table'[4] ) = "category4", 1 ),
    "category5", IF ( MAX ( 'main table'[5] ) = "category5", 1 )
)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AndrejZitnay]

Hello @v-juanli-msft ,

That will work me!!

Thank you so much.

Kind regards.

Andrej