How can I use a slicer to be used as for showing report name with a pie chart

[EaglesTony]

Hi,

  I have records that have 1) Missing Start Date, 2) Missing End Date  3) Missing Assignee Name.

  I can set these 3 flags on those records that meet each of the above criteria.

 What I want to do is:

1) Have a slicer with hardcoded values of "Missing Start Date Report", "Missing End Date Report", "Missing Assignee Name Report" and when the user selects a certain report, for example "Missing End Date Report", it would show the records with the flag = "Y" (same logic for the other 2 reports as well).

2) It would be nice as well to have a Pie chart with these 3 values on it and when the user selects a slice of the pie it could filter those records as well.

[suparnababu8]

Hi @EaglesTony 

1. Creating the Slicer with Hardcoded Values

First, you’ll need to create a new table with the hardcoded values for your slicer. You can do this using the Enter Data feature in Power BI:

1. Go to the Home tab and click on Enter Data.
2. Create a table with a single column called ReportType and add the values: “Missing Start Date Report”, “Missing End Date Report”, “Missing Assignee Name Report”.
3. Name this table ReportTypes.

Next, create a measure to filter the records based on the selected report type:

SelectedReportType = SELECTEDVALUE('ReportTypes'[ReportType])

FilteredRecords =
SWITCH(
[SelectedReportType],
"Missing Start Date Report", CALCULATE(COUNTROWS('YourTable'), 'YourTable'[StartDate] = BLANK()),
"Missing End Date Report", CALCULATE(COUNTROWS('YourTable'), 'YourTable'[EndDate] = BLANK()),
"Missing Assignee Name Report", CALCULATE(COUNTROWS('YourTable'), 'YourTable'[AssigneeName] = BLANK()),
BLANK()
)

2. Creating the Pie Chart

To create the pie chart, you need to create measures for each of the missing data types:

MissingStartDateCount = CALCULATE(COUNTROWS('YourTable'), 'YourTable'[StartDate] = BLANK())
MissingEndDateCount = CALCULATE(COUNTROWS('YourTable'), 'YourTable'[EndDate] = BLANK())
MissingAssigneeNameCount = CALCULATE(COUNTROWS('YourTable'), 'YourTable'[AssigneeName] = BLANK())

Now, create a new table for the pie chart data:

1. Go to Enter Data and create a table with two columns: Category and Count.
2. Add the following rows:
   • Category: “Missing Start Date”, Count: [MissingStartDateCount]
   • Category: “Missing End Date”, Count: [MissingEndDateCount]
   • Category: “Missing Assignee Name”, Count: [MissingAssigneeNameCount]

Name this table PieChartData.

Finally, create the pie chart using the Category column for the slices and the Count column for the values. You can then set up interactions so that selecting a slice filters the records accordingly.

Putting It All Together

1. Add the ReportType slicer to your report.
2. Add a table or other visual to display the filtered records based on the FilteredRecords measure.
3. Add the pie chart using the PieChartData table.

This setup should allow you to filter records based on the selected report type and interact with the pie chart to filter records as well. Give it a try and let me know if you need any further assistance! 😊

[EaglesTony]

For this:

Now, create a new table for the pie chart data:

1. Go to Enter Data and create a table with two columns: Category and Count.
2. Add the following rows:
   • Category: “Missing Start Date”, Count: [MissingStartDateCount]
   • Category: “Missing End Date”, Count: [MissingEndDateCount]
   • Category: “Missing Assignee Name”, Count: [MissingAssigneeNameCount]

Is the data under Category with the quotes as well ? Also, is the Count column a literal value meaning text "Count:[MissingAssigneeNameCount]" ?

This might make it easier to understand here is what I have:

Table1Data:

Key            Start Date     Due Date     Assignee   Description

1                                      9/5/2024     John       

2                9/5/2024       9/10/2024    Jill            

3                9/5/2024       9/10/2024                     Test

4                1/1/2024       1/11/2024    Joe            Test2

I have a slicer based on a table called "ReportNameTable" as follows:

ReportName

Missing Start Date Report

Missing Due Date Report

Missing Description Report

Missing Assignee Report

What I want is when the user select "Missing Description Report", then Key1 and Key2 should be filtered and shown on the table visual(the input to this is Table1Data). When selecting "Missing Assignee Report", then Key3 should be filtered and shown on the table visual. If "All" is selected in the slicer then no impact on filtering.

[MFelix · Accepted solution]

Hi @EaglesTony ,

Add the following measure to your report:

Record Filter = IF(
			CONTAINSROW(
				VALUES(Categories[Category]),
				"Missing Start Date"
			),
			COUNTROWS(FILTER(
				Data,
				Data[Start Date] = BLANK()
			))
		) +
		IF(
			CONTAINSROW(
				VALUES(Categories[Category]),
				"Missing End Date"
			),
			COUNTROWS(FILTER(
				Data,
				Data[Due Date] = BLANK()
			))
		) +
		IF(
			CONTAINSROW(
				VALUES(Categories[Category]),
				"Missing Assignee Name"
			),
			COUNTROWS(FILTER(
				Data,
				Data[Assignee] = BLANK()
			))
		) +
		IF(
			CONTAINSROW(
				VALUES(Categories[Category]),
				"Missing Description Report"
			),
			COUNTROWS(FILTER(
				Data,
				Data[Description] = BLANK()
			))
		)

Now use this has a filter on the table and select is not blank,

On the Pie chart use the categories and this measure to give you the results:

See PBIX file attach.

[EaglesTony]

Where would I put the filter on the Pie Chart and Table Visual ?

I need to show the detail records that are filtered.

[MFelix]

On the table visual you would use tlit has a filter on the pie chart you use it has a value for your slices. 

[EaglesTony]

This meaure just gives me a count correct ?

[EaglesTony]

Is there a screenshot of where to place it, as I need to show the records in the table based off the slicer value. I'm thinking there must be some sort of relationship that needs to be established ?

[MFelix]

Hi @EaglesTony ,

For this you need to create a measure that picks up the filters based on the values on your selection something similar to:

Record Filter =
               SWITCH (SELECTEDVALUE(Table[Slicer value]),
                      "Missing Start Date Report", COUNTROWS(FILTER(Table, Table[Missing Start Date Report] = "Y")),
   "Missing End Date Report", COUNTROWS(FILTER(Table, Table[Missing End Date Report] = "Y"),
   "Missing Assignee Name Report", COUNTROWS(FILTER(Table, Table[Missing Assignee Name Report] = "Y"))

Now you can use this to filter out your visuals.