Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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
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
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")
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |