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

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

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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?