Hi all
I am hoping someone out there can help me as I am very new to Power BI.
I have a dataset in Excel which summarises department names and answers to questions as either pass or fail.
The department names are listed in column A. The question description is contained in each subsequent column. The answer of each question (either a pass or a fail) are displayed in the respective column and row for that question name and department. I have provided an example below.
Department | Group | Question 1 | Question 2 | Question 3 | Question 4 | Question 5 |
Department 1 | A | Pass | Fail | Pass | Fail | Pass |
Department 2 | B | Pass | Pass | Pass | Pass | Fail |
Department 3 | A | Fail | Pass | Fail | Fail | Pass |
Department 4 | C | Pass | Pass | Pass | Pass | Pass |
Department 5 | C | Pass | Fail | Fail | Fail | Pass |
Department 6 | B | Fail | Fail | Fail | Fail | Fail |
I would like to display this information in a dashboard whereby I can use slicers or adjoining visuals to display different agggregate views of only those questions which failed and by which department. As an example I would like to have list where I select ‘Group A’ and a connected visual, let’s say a pie chart, shows the proportion and values of failure for that particular group. Another example would be if I select a particular question, let’s say question 2 and then a connected visual loads the departments which failed.
As my dataset contains around 50 departments and about 50 questions aggregated from approximately 2000 records being able to quickly see which questions failed against a particular department or group could help me easily identify trends. Another example could be finding the question with the highest proportion of failure; again this could be noted for future improvement or further investigation.
The issue I am having is, being very new to PowerBI, I cannot figure out on my own how to depict this. To make this work using filters I would need multiple filters on the page. This is fine however filters seem to work like AND statements and for the filter to work all conditions of each filter have to be met. Thus if I place a filter for question 1 and 2 on the page and set them both to Fail, I will not see any department which passed or failed merely the department which failed both questions. I have tried to edit the interaction by removing the link between the two filters but this did not work. I guess that I am after something which depicts an OR statement that is show me only those questions were marked as failed irrespective of the previous filters.
Any help would be humbly appreciated!
Thank you
