Change column to show in a table visual with slicer

[tyler_ng]

Hi, I am trying to create a report where selection in slicer would change the column shown in the table visual:

Fact table:

buyer	end user	sales	subitem1	subitem2
A	A	10	aa	aaa
A	A	15	aa	bbb
A	A	8	bb	ccc
A	A	7	cc	bbb
B	B1	20	bb	aaa
B	B2	15	bb	ccc
C	C	16	ee	ddd

Slicer: subitem1 or subitem2

When subitem1 is selected, result table:

buyer	end user	sales	subitem1
A	A	25	aa
A	A	8	bb
A	A	7	cc
B	B1	20	bb
B	B2	15	bb
C	C	16	ee

When subitem2 is selected, result table:

A	A	10	aaa
A	A	22	bbb
A	A	8	ccc
B	B1	20	aaa
B	B2	15	ccc
C	C	16	ddd

I have tried these DAX formulas but either gave the results I wanted:
Dynamic subitem = IF(HASONEVALUE(Slicer[Parameter]);SWITCH(VALUES(Slicer[Parameter]);"subitem1";SELECTEDVALUE(fact_tbl[subitem1]);"subitem2";SELECTEDVALUE(fact_tbl[subitem2])))

OR

IF(HASONEVALUE(Slicer[Parameter]);SWITCH(VALUES(Slicer[Parameter]);"subitem1";VALUES(fact_tbl[subitem1]);"subitem2";VALUES(fact_tbl[subitem2]))) 

Would really appreciate your help.

Kind regards,
Tyler

[Smauro · Accepted solution]

It is possible, but it is ugly because it displays duplicate data.

First, you will need to add an index if you already don't have one in your data (easily done in Query Editor).

Then, you need to do the slicer as @v-yuezhe-msft mentioned, or with this measure:

selectedValue =
VAR x =
    SELECTEDVALUE ( Slicer[Parameter], "Error" )
RETURN
    IF (
        x = "subitem1",
        SELECTEDVALUE ( fact_tbl[subitem1] ),
        IF ( x = "subitem2", SELECTEDVALUE ( fact_tbl[subitem2] ), x )
    )

And then, you need to add a new measure which will compute the sales based either on buyer&end user&(subitem1 or subitem2). I've managed to write this:

SalesNew =
VAR x =
    SELECTEDVALUE ( Slicer[Parameter], "Error" )
VAR y = [selectedValue]
RETURN
    IF (
        x = "subitem1",
        CALCULATE (
            SUM ( 'fact_tbl'[sales] ),
            FILTER (
                ALL ( 'fact_tbl' ),
                y = [subitem1]
                    && SELECTEDVALUE ( 'fact_tbl'[buyer] ) = [buyer]
                    && SELECTEDVALUE ( 'fact_tbl'[end user] ) = [end user]
            )
        ),
        IF (
            x = "subitem2",
            CALCULATE (
                SUM ( 'fact_tbl'[sales] ),
                FILTER (
                    ALL ( 'fact_tbl' ),
                    y = [subitem2]
                        && SELECTEDVALUE ( 'fact_tbl'[buyer] ) = [buyer]
                        && SELECTEDVALUE ( 'fact_tbl'[end user] ) = [end user]
                )
            ),
            x
        )
    )

Last, you'll need a table which includes the index. for example [index], [buyer], [selectedValue], [SalesNew]

This table will show you the added sales' amount, yet it will show it as many times as subitem1or2 exists on your data.

It's not very nice, sorry.

[Smauro · Accepted solution]

I've managed to find a way to remove the duplicates.
You will need a measure which checks if there's more than one row with the same [buyer], [end user] and [subitem1] or [subitem2]

What I've managed to come up with is this:

ReDup =
 VAR x =
     SELECTEDVALUE ( Slicer[Parameter], "Error" )
 VAR i =
     SELECTEDVALUE ( fact_tbl[Index] )
 VAR y =
     SELECTEDVALUE ( fact_tbl[buyer] )
 VAR z =
     SELECTEDVALUE ( fact_tbl[end user] )
 VAR a =
     IF (
         x = "subitem1",
         SELECTEDVALUE ( fact_tbl[subitem1] ),
         IF ( x = "subitem2", SELECTEDVALUE ( fact_tbl[subitem2] ) )
     )
 VAR k =
     IF ( x = "subitem1", 1, IF ( x = "subitem2", 2, 0 ) )
 VAR w =
     CALCULATE (
         MIN ( [Index] ),
         FILTER (
             ALL ( fact_tbl ),
             y = fact_tbl[buyer]
                 && z = fact_tbl[end user]
                 && ( ( a = fact_tbl[subitem1]
                 && k = 1 )
                 || ( a = fact_tbl[subitem2]
                 && k = 2 ) )
         )
     )
 VAR v =
     CALCULATE (
         MAX ( [Index] ),
         FILTER (
             ALL ( fact_tbl ),
             y = fact_tbl[buyer]
                 && z = fact_tbl[end user]
                 && ( ( a = fact_tbl[subitem1]
                 && k = 1 )
                 || ( a = fact_tbl[subitem2]
                 && k = 2 ) )
         )
     )
 RETURN
     IF ( w = v, "Show", IF ( i = w, "Show", "Duplicate" ) )

Creating this, and simply adding it to your Visual level filters, by filtering either by [Contains: "S"] or [Does not contain: "D"] the rows visible will not contain duplicates of this kind. Note that the index column is still needed to be shown.


Have a nice weekend

[v-yuezhe-msft]

@tyler_ng,

My Slicer table  is as follows.


Then create the following measure in fac_tbl table.

selectedValue = 
IF (
    ISFILTERED ( Slicer[Parameter]) && HASONEVALUE (Slicer[Parameter] );
     if(LASTNONBLANK (Slicer[Parameter];0 )="subitem1";
   FIRSTNONBLANK(fact_tbl[subitem1];fact_tbl[subitem1]);
   FIRSTNONBLANK(fact_tbl[subitem2];fact_tbl[subitem2])
   )
  ;
     BLANK()
)

Regards,
Lydia

[tyler_ng]

@v-yuezhe-msft thanks so much for the solution. However, could you show me how I could sum these 2 lines into 1:

If I tried to sum fact_tbl[Sales], it would drop out some lines from subitem1

This is the result I would expect from selecting subitem1

buyer	end user	sales	subitem1
A	A	25	aa
A	A	8	bb
A	A	7	cc
B	B1	20	bb
B	B2	15	bb
C	C	16	ee

[Smauro · Accepted solution]

It is possible, but it is ugly because it displays duplicate data.

First, you will need to add an index if you already don't have one in your data (easily done in Query Editor).

Then, you need to do the slicer as @v-yuezhe-msft mentioned, or with this measure:

selectedValue =
VAR x =
    SELECTEDVALUE ( Slicer[Parameter], "Error" )
RETURN
    IF (
        x = "subitem1",
        SELECTEDVALUE ( fact_tbl[subitem1] ),
        IF ( x = "subitem2", SELECTEDVALUE ( fact_tbl[subitem2] ), x )
    )

And then, you need to add a new measure which will compute the sales based either on buyer&end user&(subitem1 or subitem2). I've managed to write this:

SalesNew =
VAR x =
    SELECTEDVALUE ( Slicer[Parameter], "Error" )
VAR y = [selectedValue]
RETURN
    IF (
        x = "subitem1",
        CALCULATE (
            SUM ( 'fact_tbl'[sales] ),
            FILTER (
                ALL ( 'fact_tbl' ),
                y = [subitem1]
                    && SELECTEDVALUE ( 'fact_tbl'[buyer] ) = [buyer]
                    && SELECTEDVALUE ( 'fact_tbl'[end user] ) = [end user]
            )
        ),
        IF (
            x = "subitem2",
            CALCULATE (
                SUM ( 'fact_tbl'[sales] ),
                FILTER (
                    ALL ( 'fact_tbl' ),
                    y = [subitem2]
                        && SELECTEDVALUE ( 'fact_tbl'[buyer] ) = [buyer]
                        && SELECTEDVALUE ( 'fact_tbl'[end user] ) = [end user]
                )
            ),
            x
        )
    )

Last, you'll need a table which includes the index. for example [index], [buyer], [selectedValue], [SalesNew]

This table will show you the added sales' amount, yet it will show it as many times as subitem1or2 exists on your data.

It's not very nice, sorry.

[BarryM]

I am having a similar issue. I am trying to create a costing sheet that displays the total costs by fiscal year selected in the slicer. I am having a great deal of trouble with this as I cannot use the "UNPIVOT" method.

[Smauro · Accepted solution]

I've written a reply but I'm unable to see it. I'm posting it again, if it comes up as a duplicate, I'm sorry.

I've managed to exclude duplicates. It's not easy, but it's possible.
You need to check for each row if it's the first row with the same [buyer], [end user] and [subitem1(or2)].

A measure I've created is this:

ReDup = 
VAR x =
    SELECTEDVALUE ( Slicer[Parameter], "Error" )
VAR i =
    SELECTEDVALUE ( fact_tbl[Index] )
VAR y =
    SELECTEDVALUE ( fact_tbl[buyer] )
VAR z =
    SELECTEDVALUE ( fact_tbl[end user] )
VAR a =
    IF (
        x = "subitem1",
        SELECTEDVALUE ( fact_tbl[subitem1] ),
        IF ( x = "subitem2", SELECTEDVALUE ( fact_tbl[subitem2] ) )
    )
VAR k =
    IF ( x = "subitem1", 1, IF ( x = "subitem2", 2, 0 ) )
VAR w =
    CALCULATE (
        MIN ( [Index] ),
        FILTER (
            ALL ( fact_tbl ),
            y = fact_tbl[buyer]
                && z = fact_tbl[end user]
                && ( ( a = fact_tbl[subitem1]
                && k = 1 )
                || ( a = fact_tbl[subitem2]
                && k = 2 ) )
        )
    )
VAR v =
    CALCULATE (
        MAX ( [Index] ),
        FILTER (
            ALL ( fact_tbl ),
            y = fact_tbl[buyer]
                && z = fact_tbl[end user]
                && ( ( a = fact_tbl[subitem1]
                && k = 1 )
                || ( a = fact_tbl[subitem2]
                && k = 2 ) )
        )
    )
RETURN
    IF ( w = v, "Show", IF ( i = w, "Show", "Duplicate" ) )

By using this as a visual filter, you'd be able to filter out the duplicates. You'll need however an index column displayed.

Have a nice weekend 🙂

[tyler_ng]

@Smauro thanks so much, it works. I tested with my sample, hopefully it works the same with my real dataset. Thanks to your formulas, I learned alot about DAX.

[Smauro · Accepted solution]

I've managed to find a way to remove the duplicates.
You will need a measure which checks if there's more than one row with the same [buyer], [end user] and [subitem1] or [subitem2]

What I've managed to come up with is this:

ReDup =
 VAR x =
     SELECTEDVALUE ( Slicer[Parameter], "Error" )
 VAR i =
     SELECTEDVALUE ( fact_tbl[Index] )
 VAR y =
     SELECTEDVALUE ( fact_tbl[buyer] )
 VAR z =
     SELECTEDVALUE ( fact_tbl[end user] )
 VAR a =
     IF (
         x = "subitem1",
         SELECTEDVALUE ( fact_tbl[subitem1] ),
         IF ( x = "subitem2", SELECTEDVALUE ( fact_tbl[subitem2] ) )
     )
 VAR k =
     IF ( x = "subitem1", 1, IF ( x = "subitem2", 2, 0 ) )
 VAR w =
     CALCULATE (
         MIN ( [Index] ),
         FILTER (
             ALL ( fact_tbl ),
             y = fact_tbl[buyer]
                 && z = fact_tbl[end user]
                 && ( ( a = fact_tbl[subitem1]
                 && k = 1 )
                 || ( a = fact_tbl[subitem2]
                 && k = 2 ) )
         )
     )
 VAR v =
     CALCULATE (
         MAX ( [Index] ),
         FILTER (
             ALL ( fact_tbl ),
             y = fact_tbl[buyer]
                 && z = fact_tbl[end user]
                 && ( ( a = fact_tbl[subitem1]
                 && k = 1 )
                 || ( a = fact_tbl[subitem2]
                 && k = 2 ) )
         )
     )
 RETURN
     IF ( w = v, "Show", IF ( i = w, "Show", "Duplicate" ) )

Creating this, and simply adding it to your Visual level filters, by filtering either by [Contains: "S"] or [Does not contain: "D"] the rows visible will not contain duplicates of this kind. Note that the index column is still needed to be shown.


Have a nice weekend