Creating a Matrix Based on the Column Values - Categorical Data

[emman_burgonia]

Hello, 

I am pretty new and Power BI and I am researching a lot but I'm having trouble creating a solution for this. I have this table in Power Query 

And I'm trying to produce something like this:

I'm looking for the COUNTIFS function but I am not sure what is its equivalent in DAX. Thanks for your help. 

[PaulDBrown · Accepted solution]

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