Forum Discussion
Continued ContributorCreating a slicer from unflattened data
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!
Solution:
Per the suggestion from Fowmy here is the step-by-step process for those less familiar with PowerBI:
- In PowerBI desktop click "Home" on the menu bar and select "Transform Data" to bring up Power Query editor
- 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.)
- Rename "Type" and "Attribute" to whatever you like
- 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:
- 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%") )
3 Replies
- Fowmy
Super User
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 🙂
mrothschildAppreciate 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
Solution:
Per the suggestion from Fowmy here is the step-by-step process for those less familiar with PowerBI:
- In PowerBI desktop click "Home" on the menu bar and select "Transform Data" to bring up Power Query editor
- 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.)
- Rename "Type" and "Attribute" to whatever you like
- 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:
- 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%") )
