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