How can I get my slicers to "override" the data shown in my graph?

[villasenorbritt]

I am making a report that displays a replica of a program my company has that displays thickness of their wood products. I have statuses that identify whether data is "good" or "bad". Anything with a status of 5 is "good", but that does not mean that anything from 1-4 is necesarily bad. For example, a status of 2 indicates that it was a first cut of a piece of wood. I have a chart that displays all of the good data and a slicer with first cut and second cut labels. So my good data formula is 

\\\ 

AnyIssue = if(AND(AND(AND(AND(vwSummaryDataItem[ThicknessStatusFK]=5, vwSummaryDataItem[DebrisStatusFK]=5), vwSummaryDataItem[NotchStatusFK]=5), vwSummaryDataItem[TopBottomStatusFK]=5),vwSummaryDataItem[ShortDataStatusFk]=5), "No Issue", "Issue")

\\\

The issue is that I can not slice first cuts and second cuts since they are categorized as bad data, but I also can't include them in good data because it throws my numbers off. They want me to make sure my numbers align with the program and they only do if I do not include status if 2 in the good data. Is there a way around this to where I could still show my cuts on the graph? Or is this more an issue with the design of the database?

[Anonymous]

Hi @villasenorbritt,

You can add if statement to check the slicer selections and redirect to different expressions to achieve your requirement.

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[villasenorbritt]

@Anonymous Thank you for your response. Here is some sample data to make it easier to get a solution. 

Sample #	AVG Thickness	AVG STDEV	TopBottomStatus	ThicknessStatus	DebrisStatus	DataStatus	Cuts
1	1.21	0.023	3	5	5	Issue	Null
2	1.23	0.022	2	5	5	Issue	First Cut
3	1.11	0.021	5	5	5	No Issue	2nd Cut
4	1.21	0.2	5	4	4	Issue	2nd Cut
5	1.22	0.19	2	5	5	Issue	1st Cut

I have multiple slicers. 

1. Cuts -- 1st or 2nd 

2. Data Issue -- No Issue or Issue

3. One for each of the data issues labeled as Thickness Issue, Debris Issue, ETC.

"Good Data" is anything with a status that is equal to 5 (I have some listed in this formula that are not in the sample data. I'm connectd to SQL so I made a quick table.) Below is how the formula is supposed to look:

AnyIssue = if(AND(AND(AND(AND(vwSummaryDataItem[ThicknessStatusFK]=5, vwSummaryDataItem[DebrisStatusFK]=5), vwSummaryDataItem[NotchStatusFK]=5), vwSummaryDataItem[TopBottomStatusFK]=5),vwSummaryDataItem[ShortDataStatusFk]=5), "No Issue", "Issue")

It has to be all statuses equal to five in order to be good data. The slicer that I'm mostly concerned with is the Cuts slicer. The formula is:

Cuts = If(vwSummaryDataItem[TopBottomStatusFK] = 2, "1st Cut", If(vwSummaryDataItem[TopBottomStatusFK] = 5, "2nd Cut", blank() ))

My ultimate goal would be to find a way to get rid of the Data Issues slicer, and have it to where the graph would show the Good Data automatically and then have 1st and 2nd cuts be visualized when selected. Same with the Thickness Issue, Debris Issue. I'd like those to populate when selected. The problem is that since TopBottomStatus has the 1st cuts with a status = 2, I can't get them to show up. 

If that is not possible, my backup plan is to keep the Data Issues Slicer. BUT, as of now, I have my Good Data fornula as below since I can not get 1st cuts to show up otherwise:

AnyIssue = if(AND(AND(AND(AND(vwSummaryDataItem[ThicknessStatusFK]=5, vwSummaryDataItem[DebrisStatusFK]=5), vwSummaryDataItem[NotchStatusFK]=5), vwSummaryDataItem[TopBottomStatusFK]<>3),vwSummaryDataItem[ShortDataStatusFk]=5), "No Issue", "Issue")

As you can see, I have the TopBottomStatus <>3 in order to allow the 1st cuts to populate on the graph when selected. TopBottomStatus only has three options, with 2 being 1st cuts and 5 being 2nd cuts as well as an indicator of "Good Data". This was a work around, but is technically incorrect, although my numbers are not effected. I know this is kind of complicated, but I would greatly appreciate any help you can give me. 

[parry2k]

@villasenorbritt I would recommend adding another column that has the logic of what you want to include in the visual, that way your existing status column will not have any impact on other visuals on the reports.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[villasenorbritt]

@parry2k Do you know how I could create a column that does the AVERAGE of standard deviation, but with filters such as TopBottomStatus = 5? I've tried multiple different ways with no luck. I see that the AverageX can be done with filters, but when I attempt it, I get all the same answer in my columns. 

[villasenorbritt]

I'm not sure I get what you mean. Could you further explain? I've included my visual in case that helps.