cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rjstreet Frequent Visitor
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

Accepted Solutions
OwenAuger Super Contributor
Super Contributor

Re: Dynamic Column Based on Slicer Selection

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 Smiley Happy



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




6 REPLIES 6
rjstreet Frequent Visitor
Frequent Visitor

Re: Dynamic Column Based on Slicer Selection

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

OwenAuger Super Contributor
Super Contributor

Re: Dynamic Column Based on Slicer Selection

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 Smiley Happy



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




rjstreet Frequent Visitor
Frequent Visitor

Re: Dynamic Column Based on Slicer Selection

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

BiMK Regular Visitor
Regular Visitor

Re: Dynamic Column Based on Slicer Selection

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

Re: Dynamic Column Based on Slicer Selection

Hi @BiMK

 

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 Smiley Happy

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




mscampos Regular Visitor
Regular Visitor

Re: Dynamic Column Based on Slicer Selection

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?

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 103 members 1,754 guests