Creating a Matrix Based on the Column Values - Categorical Data

Ok, let's take this step by step...

A) We need to unpivot the columns in Power Query. This involves

1. Creating an index column
2. Selecting the index column, right click and select "Unpivot other columns"
3. Deleting the index column
4. deleting blank rows from the value column and renaming the facilities column

Like this:

B) Creating dimension tables for both Facility and Value field:

1. Select the facility colum, right click and select "Add as new query"
2. Convert this list into a table by clicking on the "Convert to Table" in the ribbon (no aggregation)
3. Select the column and Remove duplicates under "Remove Rows" in the ribbon
4. Rename the field and table

Like this:

Repeat the process for the "Value" field and load into the model

C) Create relationships between each field in the Dimnesion Tables and their corresponding field in the Fact table (In my example named "Facilities Table")

D) Build the matrix:

        1. Create the measure to count the Values:

Count Value Measure = COUNT('Facilities Table'[Value])

       2. Build the matrix using the fields from the dimension tables: dFacility in the Rows bucket; dValue in the Columns Bucket; and add the [Count Value Measure] to the values bucket to get:

I've attached the sample PBIX file