Forum Discussion

rjstreet's avatar
rjstreet
Frequent Visitor
9 years ago
Solved

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?

  • OwenAuger's avatar
    OwenAuger
    9 years ago

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

9 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

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

         

    • rjstreet's avatar
      rjstreet
      Frequent Visitor
      9 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        OwenAuger
        Super User
        9 years ago

        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's avatar
    rjstreet
    Frequent Visitor
    9 years ago

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

  • tschris's avatar
    tschris
    Regular Visitor
    3 years ago

    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.