Creating a slicer from unflattened data

[mrothschild]

Hi, 

Quick summary is trying to create a slicer to effectively hide columns in a Matrix visual, based by linking a column of unique identifiers to column headers.

Link to an example spreadsheet: https://drive.google.com/file/d/1F27asYn8nblMrMwR6-_PJ6IqmkjVtMRB/

My primary data has columns in the format like this (abridged):

Asset ID Appraisal Date	Asset ID	Year	Appraisal Date	HLMV 0%	HLMV 1.5%	HLBV 0%	HLBV 1.5%	HLSV 0%	HLSV 1.5%	FLMV 0%	FLMV 1.5%	FLBV 0%	FLBV 1.5%	FLSV 0%	FLSV 1.5%
A461 2019-12-31	A461	12/31/2019	12/31/2019	$3.13	$0.00	$2.44	$0.00	$2.01	$0.00	$4.12	$0.00	$3.43	$0.00
A461 2019-12-31	A461	12/31/2020	12/31/2019	$2.87	$2.92	$2.24	$2.27	$1.84	$1.87	$3.84	$3.88	$3.20	$3.24
A461 2019-12-31	A461	12/31/2021	12/31/2019	$2.64	$2.72	$2.05	$2.12	$1.69	$1.74	$3.58	$3.66	$2.99	$3.06
A461 2019-12-31	A461	12/31/2022	12/31/2019	$2.42	$2.53	$1.88	$1.97	$1.55	$1.62	$3.33	$3.44	$2.80	$2.89

My slicer data looks like this (abridged): 

Condition	Inflation	Status	Full Scenario
Base	0.0%	Full	FLBV 0%
Base	1.5%	Full	FLBV 1.5%
Base	2.0%	Full	FLBV 2%
Max	0.0%	Full	FLMV 0%
Max	1.5%	Full	FLMV 1.5%

In the current format, I can't link "Full Scenario" from the slicer data with the column headers in the primary headers, so I think I need to make a calculated column or table?   But not sure what the steps are or how to execute such an operation, or if there's a simpler solution.

The primary data is currently in a Matrix visual, with each of the column headers from the primary data.  But I'd like to be able to slice at a minimum by the "Full Scenario" in the slicer data table, but ideally with a hierarchy slicer using two of the three columns.

Thanks!

[Fowmy]

@mrothschild 

In Power Query, you need to UnPivot all the columns after "Appraisal Column" from Columns to Rows. Then, a relationship can be created between the tables to achieve what you are expecting. 

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[mrothschild]

Appreciate your response @Fowmy , and I see how that works for creating the slicer, but then creates a different problem with data aggregation in the Matrix.  In the linked spreadsheet I show "Asset ID", e.g., A461, A472, and B8189.  

What I need from the data is that when I drill up hierarchies, the value will be specific to Asset ID, and then to "Project" (not shown, but Projects are "A" and "B").  With your methodology, when I drill up in the Matrix, it's aggregating all of the Asset IDs and Appraisal scenarios, not just those tied to specific assets or Projects.

I think I need to create a Calculated Measure with the Value column?

Thanks!

[mrothschild · Accepted solution]

Solution:

Per the suggestion from @Fowmy here is the step-by-step process for those less familiar with PowerBI:

1. In PowerBI desktop click "Home" on the menu bar and select "Transform Data" to bring up Power Query editor
2. In the table, use CTRL to select the columns with the different headers that contain JUST the data (e.g,, HLBV 0%, HLBV 1.5%, HLBV 2.0%, etc.)
3. Rename "Type" and "Attribute" to whatever you like
4. Close & Apply to return to PowerBI

There are two ways to progress from here.

(A) In the Matrix Columns hierarchy, include "Attribute" from the primary data table.

Then in the Values section of the Matrix, the "Values" from the primary data table

The other columns beneath can be further detailed data (e.g., Project --> Asset)

(B) In PowerBI, create a calculated measure which looks like below:

1. Use the newly-created individual measures as the Values in the Matrix Table

HLBV 0% = 
CALCULATE(
	SUM('Aggregated Appraisals'[Appraised Value]),
    ALLSELECTED('Aggregated Appraisals'[Appraisal Scenario]),
    FILTER('Aggregated Appraisals','Aggregated Appraisals'[Appraisal Scenario]="HLBV 0%")
    
)