Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rjstreet
Frequent Visitor

Dynamic Column Based on Slicer Selection

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?

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

9 REPLIES 9
tschris
Regular Visitor

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
Super User
Super User

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 🙂


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
Anonymous
Not applicable

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:

IDNameJob TitleCountry
1001AManagerUSA
1002BManagerCanada
1003CManagerUSA
1004DCEOSout Africa
1005ECEOChina
1006FAssistantCanada
1007GAssistantIndia
1008HManagerCanada
1009ICEOCanada
1010JManagerChina

Fact Table:

IDNameExpensesDay
1001A19555day 1
1002B68287day 1
1003C89763day 1
1001A45877day 2
1005E69871day 2
1001A36533day 3
1007G62252day 2
1008H24095day 2
1009I80338day 2
1008H15994day 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

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

 

Anonymous
Not applicable

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
Not applicable

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 

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 🙂

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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

rjstreet
Frequent Visitor

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.