cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Slicer out of two data sources

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?

Slicer question.JPG

Many thanks.

 

Kind regards.

 

Andrej

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Slicer out of two data sources

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

12.png

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

11.png

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.

View solution in original post

4 REPLIES 4
Community Support Team
Community Support Team

Re: Slicer out of two data sources

Hi @AndrejZitnay 

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

4.png

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.

Re: Slicer out of two data sources

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.

Slicer question 2.JPG

 

Many thanks.

 

Kind regards.

 

Andrej

 

 

 

 

 

 

 

Community Support Team
Community Support Team

Re: Slicer out of two data sources

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

12.png

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

11.png

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.

View solution in original post

Re: Slicer out of two data sources

Hello @v-juanli-msft ,

 

That will work me!!

 

Thank you so much.

 

Kind regards.

 

Andrej

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 177 members 1,886 guests
Please welcome our newest community members: