Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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.
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
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,
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
@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 |
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.
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.
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 🙂
@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.
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
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |