Forum Discussion
Creating a Matrix Based on the Column Values - Categorical Data
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.
Ok, let's take this step by step...
A) We need to unpivot the columns in Power Query. This involves
- Creating an index column
- Selecting the index column, right click and select "Unpivot other columns"
- Deleting the index column
- deleting blank rows from the value column and renaming the facilities column
Like this:
B) Creating dimension tables for both Facility and Value field:
- Select the facility colum, right click and select "Add as new query"
- Convert this list into a table by clicking on the "Convert to Table" in the ribbon (no aggregation)
- Select the column and Remove duplicates under "Remove Rows" in the ribbon
- 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
1 Reply
Ok, let's take this step by step...
A) We need to unpivot the columns in Power Query. This involves
- Creating an index column
- Selecting the index column, right click and select "Unpivot other columns"
- Deleting the index column
- deleting blank rows from the value column and renaming the facilities column
Like this:
B) Creating dimension tables for both Facility and Value field:
- Select the facility colum, right click and select "Add as new query"
- Convert this list into a table by clicking on the "Convert to Table" in the ribbon (no aggregation)
- Select the column and Remove duplicates under "Remove Rows" in the ribbon
- 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