Dynamic Column Based on Slicer Selection

[rjstreet]

I have a situation where users want the ability to have a column change the data presented based on a slicer selection.  The analogy would be:

• Table A: Sales (which is linked to buyer and seller)
• Table B: Buyer
• Table C: Seller

In this example, the users have a table of sales data (from table A), but the first column can be either a buyer name (from table B) or a seller name (from table C) with the other columns being the sum for either the buyer or seller shown in the current row.  Table A is linked to B and C.  I've tried several different approaches (particularly with a standalone table with "Buyer" and "Seller" for the slicer and a measure that gives the current selection), but it seems like DAX functions within calculated columns can't detect filter changes.  Any thoughts on how to do this?

[tschris]

I had to solve this problem myself with a list of 42 columns where I needed to have a single page to plot the sales per day, the items sold per day, the complaints per day etc... the comon dimension was the date but the Y azis had to change from column A to columns B etc.

Since it took me a while to make it happen, I desided to post an example with a dummy sales dataset here. I hope it helps anyone that might face the same issue.

[OwenAuger · Accepted solution]

Hi @rjstreet

A couple of ideas that you can experiment with:

(PBIX file demonstrating these uploaded here)

1. My preferred option:
   • Combine Tables B (Buyer) & C (Seller) into a single BuyerSeller table, with an additional "Type" column specifying whether each row is a Buyer or a Seller.
   • Then you can use a slicer on Type to choose between Buyer/Seller.
   • No 'tricky' DAX is required.
2. Similar to your idea:
   • Keep Tables B (Buyer) & C (Seller) separate.
   • Make the relationship with Table B active, but the relationship with Table C inactive.
   • Create a disconnected table to choose between Buyer and Seller, with a corresponding measure to harvest the selected value.
   • Set up your Sales measures so that if Buyer is selected, calculation is performed as usual, but if Seller is selected, the relationship with Table B is cleared and the relationship with Table C is activated (using USERELATIONSHIP).
   • With this method, you have to explicitly filter out (blanks) from any filters on Tables B & C, so that when Buyer is selected, all Sellers disappear and vice versa.
   • You will end up with measures that look like:
     

     Sales Amount = 
     SWITCH (
         [BuyerSeller Selected],
         "Buyer", SUM ( Model2_Sales[Sales] ),
         "Seller", CALCULATE (
             SUM ( Model2_Sales[Sales] ),
             ALL ( Model2_Buyer ),
             USERELATIONSHIP ( Model2_Sales[BuyerSeller], Model2_Seller[Seller] )
         )
     )

Anyway, these are just ideas - see if they are of any use.

Owen 🙂

[Anonymous]

Hi @OwenAuger  I m trying to implement this solution in to my problem but can't help my self.

I have two tables Dimensions and Fact.

Dimension Table:

ID	Name	Job Title	Country
1001	A	Manager	USA
1002	B	Manager	Canada
1003	C	Manager	USA
1004	D	CEO	Sout Africa
1005	E	CEO	China
1006	F	Assistant	Canada
1007	G	Assistant	India
1008	H	Manager	Canada
1009	I	CEO	Canada
1010	J	Manager	China

Fact Table:

ID	Name	Expenses	Day
1001	A	19555	day 1
1002	B	68287	day 1
1003	C	89763	day 1
1001	A	45877	day 2
1005	E	69871	day 2
1001	A	36533	day 3
1007	G	62252	day 2
1008	H	24095	day 2
1009	I	80338	day 2
1008	H	15994	day 1

Slicer Table:

Country, Job Title, N...

When i select Country, the chart should shows

• Countries (list of coutnries) and Sum of Expenses

but if i switch to Job title in slicer, the chart will show

• Job title (list of job titles ) and Sum Expenses

Note: 

1. I am already using Bookmark solution but it takes lots of time to create new bookmark if requirment is changed during run time. I m looking for automated solution so i don't need to worry if dynamic dimenesion (column) increased or decreased.
2. I have alreay tried (unpivot method), it is not efficent since i have more columns in my dimenstion table then i have mentioned here in example, Unpivot method will increase rows in my table size and also reduce the performace.
3. I am using direct query. If any extra tables need to be created i can get it done in direct query (but not unpivot method)

Thanks,

Kulchandra

[surya_gupta]

did you find the solution, please share if you did. i am also facing the same problem

[Anonymous]

Hello.

I'm trying to use slicer like a parameter, if a click two times at same slicer item I uncheck the item.

for example, slicer with 2 itens, if I click on item A, only data from item A will show, if I click on item A again, all data will show.

Any Idea?

[Anonymous]

Hey @OwenAuger

I downloaded the file you sent, but I can't seem to figure out how does this work? You only use columns from the Model1_Sales table and the table below still changes depending on the slicer filter selected. Can you please explain this to me? I really need to implement this in my report.

Thanks 

[OwenAuger]

Hi @Anonymous

Go to Relationships view and it should be clearer.

The BuyerSeller column of the Model1_Sales table is related to the Name column of the BuyerSeller table.

The BuyerSeller table also contains a column Type which indicates whether the current rows is a Buer or Seller. I used this in a slicer to switch between Buyers & Sellers.

Owen 🙂

[rjstreet]

We're using the first option - it's a bit clunky conceptually, but gives the appropriate responses nicely!

[rjstreet]

Alternatively, being able to swap out the visual based on a slicer selection would work as well (though we don't seem to have a way to do this currently).