0

I have a fairly large (long) dataset that looks something like this... one record per individual response to each part of each question:

ID breakdown_1 breakdown_2 question question_A_or_B value
1001 grp_A grp_B 1 A 5
1001 grp_A grp_B 1 B 6
1001 grp_A grp_B 2 A 4
1001 grp_A grp_B 2 B 6
1002 grp_A grp_A 1 A 4
1002 grp_A grp_A 1 B 7
1002 grp_A grp_A 2 A 3
1002 grp_A grp_A 2 B 5
1003 grp_B grp_B 1 A 3
1003 grp_B grp_B 1 B 5
1003 grp_B grp_B 2 A 5
1003 grp_B grp_B 2 B 2
1004 grp_B grp_A 1 A 4
1004 grp_B grp_A 1 B 7
1004 grp_B grp_A 2 A 3
1004 grp_B grp_A 2 B 2
1005 grp_A grp_C 1 A 4
1005 grp_A grp_C 1 B 3
1005 grp_A grp_C 2 A 5
1005 grp_A grp_C 2 B 5

I'm creating a report in Power BI that uses a clustered column chart to compare the averages of value between question part A and question part B. The user has the abilities to select a question (accomplished by a slicer) and to select which breakdown column they would like (accomplished by a parameter - feeding the X axis of the column chart). This is what that chart looks like: example clustered column chart, without suppression in place

Due to legal requirements, I must suppress any aggregated data points representing fewer than a certain number of respondents. For the sake of this example, let's say that minimum distinct count of respondents is 2; that would mean that, if the user has selected breakdown_2 in the parameter, the columns for both questions A & B for grp_C would need to be removed from that particular visual. Importantly, I cannot simply remove those respondents from the dataset, as they need to be counted in the aggregations by breakdown_1 as well. To accomplish this, I created a few measures:

CountD_resp = DISTINCTCOUNTNOBLANK(Sheet1[ID])  -- distinct count of respondents
Suppress_Counts = IF(CALCULATE([CountD_resp], ALLSELECTED('Select Breakdown'[Select Breakdown])) < 2, "Y", "N")  -- flag if distinct count is less than 2 at any aggregation
ValueAvg_supp = IF([Suppress_Counts] = "N", AVERAGE(Sheet1[value]))  -- only do aggregation where appropriate

Using the new ValueAvg_supp in my chart (instead of using value displayed as an average) functions properly... the grp_C category no longer shows in the visual, as expected: example clustered column chart, now with suppression in place

But here is my issue. I want to do this elegantly. That is, rather than simply omitting suppressed data points, I want to inform the user if this is actively happening on the visual they are currently viewing. A colleague of mine sent me some pointers, but the way he does it is that an entire visual gets suppressed if the distinct count goes below his threshold (not a single column or category), and I think this is why I couldn't get it to work. Here are the additional measures I tried, along with a card visual; if you look again at the above image, the box labeled Suppression_warning_text at the bottom is the card visual not functioning as intended:

Suppression_warning_background = IF([Suppress_Counts] = "Y", "#FFFF00", "#FFFFFF")  -- to control the background color... yellow if active
Suppression_warning_text = IF([Suppress_Counts] = "Y", "If there were fewer than 2 respondents to a given question, then that data point is suppressed for this visual.", "")

I have had to do something very similar to this in Python, which is relatively straightforward... I would feed already-aggregated data to a visual (basically in the form of a pivot table), suppress out the rows of that table based on the current requirement, have a flag that changes if suppression is actively happening, and display a block of text dynamically based on that flag.

Any ideas or guidance are appreciated. I know this is a long question, but I have not seen anything quite like this in my search for answers, and I wanted to include everything I have tried as well as a robust enough example dataset. I would just do this in Python instead, but I am being tasked with figuring these quirks out in Power BI.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.