topic Selection from Slicer as column in virtual table in DAX Commands and Tips

Selection from Slicer as column in virtual table

Pascal_KTeam — Thu, 02 Apr 2020 18:42:34 GMT

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!

Re: Selection from Slicer as column in virtual table

Greg_Deckler — Thu, 02 Apr 2020 21:00:03 GMT

Sample data would be great. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Re: Selection from Slicer as column in virtual table

Pascal_KTeam — Fri, 03 Apr 2020 05:54:26 GMT

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

RATETYPE	ACCOUNT	Business Unit	Amount
notrans	acc001	unit1	100
notrans	acc002	unit2	200
trans	acc003	unit1	300
trans	acc004	unit2	400

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.
Source	Source: manually created Excel file

Business Unit	Account
unit1	acc001
unit2	acc003
unit2	acc004

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

Re: Selection from Slicer as column in virtual table

Pascal_KTeam — Thu, 09 Apr 2020 07:30:52 GMT

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