Forum Discussion

Pascal_KTeam's avatar
Pascal_KTeam
Resolver I
6 years ago
Solved

Selection from Slicer as column in virtual table

Hi all. I'm trying my best to describe the issue I have...

 

I need to create a virtual table which includes one column that shows the selection from a slicer

 

Slicer:

 

I have a slicer to select Business Unit. I also have a measure which gives me the selected item from the slicer with SELECTEDVALUE

 

physical Table:

 

I have a table with data from different Business Units

 

virtual table:

 

I have a virtual table which is based on the physical table from above but I add a couple of columns with ADDCOLUMNS. So far all good.

 

Since I need the virtual table to be filtered to only the Business unit which is selected in the slicer, I was trying to add the result of the SELECTEDVALUE measure from above to a new column but this doesn't work. It stays blank. The formulare for the virtual table looks like this:

 

I'm looking for a way to replace the hardcoded "Business Unit XY" wich what is selected in the slicer

 

HL Virtual =

 

VAR newTable =

ADDCOLUMNS(HL_Template;

    "RATETYPE";

        LOOKUPVALUE('Data'[RATETYPE];'Data'[MG_ACCOUNT ID];HL_Template[MG_ACCOUNT]);

     "Selected Unit";

        "Business Unit XY")

VAR filteredtable =

    SELECTCOLUMNS(newTable;

        "MG_ACCOUNT";HL_Template[MG_ACCOUNT];"Unit";HL_Template[Unit];"RATETYPE";[RATETYPE];"Selected Unit";[Selected Unit];"Unit = selected Unit";IF(HL_Template[Unit]=[Selected Unit];"YES";"NO"))

 

RETURN

FILTER(filteredtable;[Unit = selected Unit]="YES")

 

I'm happy for any hints in the right direction. Thanks guys!

  • Pascal_KTeam's avatar
    Pascal_KTeam
    6 years ago

    I had to rethink the whole topic and now successfully created a measure which is doing what I need. 

    Of course, calculated columns are calculated on refresh so this wasn't actually an option

3 Replies

    • Pascal_KTeam's avatar
      Pascal_KTeam
      Resolver I

      Basically I start with 2 tables (linked Excel file might make it even clearer, at least I hope so: Excel File with details ) 

      Table name:Data  
      what's in there:all data  
      Source:ERP  
          
      RATETYPEACCOUNTBusiness UnitAmount
      notransacc001unit1100
      notransacc002unit2200
      transacc003unit1300
      transacc004unit2400

       

      and

       

      Table name:HL Template
      Wha'ts in there:template which shows the accounts that should be shown in the report. Note that for unit1 we don't want to see acc002 and therefore haven't listed it in the template. 
      SourceSource: manually created Excel file
        
      Business UnitAccount
      unit1acc001
      unit2acc003
      unit2acc004

       

      Then I create a 3rd table "Calc Table" using DAX. Basically I take table HL Template and add some columns from the Data table. This works fine. What I also need in the "Calc Table" is a column which shows which Business Unit is currently selected in a slicer which is linked to the Data table. So the red marked part of the DAX needs to be replaced

       

      Calc Table =

      VAR newTable =
      ADDCOLUMNS(HL_Template;
          "RATETYPE";
              LOOKUPVALUE('Data'[RATETYPE];'Data'[ACCOUNT];HL_Template[ACCOUNT]);
           "Selected Unit";
              "Business Unit XY")
      VAR filteredtable =
          SELECTCOLUMNS(newTable;
              "ACCOUNT";HL_Template[ACCOUNT];"Business Unit";HL_Template[Business Unit];"RATETYPE";[RATETYPE];"Selected Unit";[Selected Unit];"Unit = selected Unit";IF(HL_Template[Unit]=[Selected Unit];"YES";"NO"))

      RETURN
      FILTER(filteredtable;[Unit = selected Unit]="YES")

       

      • Pascal_KTeam's avatar
        Pascal_KTeam
        Resolver I

        I had to rethink the whole topic and now successfully created a measure which is doing what I need. 

        Of course, calculated columns are calculated on refresh so this wasn't actually an option