Question: I am writing a scalar plpgsql function that contains a C function that has a side-effect outside of the database. When the function is invoked, in some arbitrary SQL (trigger, select, write, etc), I want the side-effect to be committed or rolled back on the PostgreSQL unit of work (UOW) boundary. I can handle the UOW commit, but I don't know how to "catch" the database ROLLBACK and rollback the side-effect. The key point is I am writing the function, but don't have control of how it is called, i.e., I can not "force" the call to be in a block with EXCEPTION handlers. Any ideas?

For the commit, I plan to have the plpsql function INSERT into a database TABLE that has a trigger "... AFTER INSERT ... EXECUTE PROCEDURE commit\_my\_side\_effect()", so when the UOW is committed, the row is committed, the AFTER INSERT trigger fires and presto, the side effect is committed;

The only idea I have so far is to pass out the txid\_current() to a background worker process. Then on some heartbeat using SPI, check if the txid is not in flight or committed, then it must have been rolled back. But that seems like heavy lifting.

Answer: 
Basically this is due to the way the box and whisker chart is working. The visuals needs a dataset to calculate the values (mean, median, etc.) and use those values to show the box and whisker.

So in your case you need to create a measure that is on the same level as the scores (because those values are needed) and is only available. See the screenshot for a visual explanation of the needed measure.

[![enter image description here](https://i.stack.imgur.com/W4zeZ.png)](https://i.stack.imgur.com/W4zeZ.png)

I created the measure with the follow DAX measure:

`Filter Score = IF(CALCULATE(DISTINCTCOUNT(Courses[Score]), ALLEXCEPT(Courses, Courses[Course]))>4, MIN(Courses[Score]), BLANK())`

The Boolean expression of the `IF` statement calculates the distinct scores per course via a `CALCULATE` expression and the `ALLEXCEPT` filter option to ignore everything but the course value. And the `TRUE` part returns the score which needs to be aggregated, so the `MIN` and the `FALSE` part return a `BLANK()` value so is can be ignored.

When you add the new measure and create a BW chart it will only show 'Physics' course results.

[![enter image description here](https://i.stack.imgur.com/nTvPg.png)](https://i.stack.imgur.com/nTvPg.png)

If you need more help, please let me know here or via email.

-JP

BTW: I just updated my PowerBI visual GitHub repository (<https://github.com/liprec/PowerBI-custom-visuals>) and added my box and whisker chart and my hierarchy slicer to it in the folder `oldAPI`.
The crux of your problem, as far as I can tell, is that you want to filter visuals to courses that have a particular number of distinct values. Which visual you want to use is almost irrelevant (though it was helpful to have a sample Power BI workbook to follow along with).

The way I'd approach this (and not saying this is the best or only way)

**Step 1**

Create a new Course dimension table, with one row for each unique course. In the sample workbook, you can click 'Enter Data' and manually type in the data.

```
Course
------
English
Math
Physics

```

**Step 2**

Next, create a calculated column in the new table and calculate the distinct count for each course. This isn't a measure - it's a column in your table, that uses the Distinct Count calculation from your question.

```
Distinct Count = CALCULATE(DISTINCTCOUNT(Courses[Score]), SUMMARIZE('Courses','Courses'[Course])) 

```

The SUMMARIZE works like a GROUP BY. In essence, creating one row per course with a distinct count of scores.

**Step 3**

Use this new attribute as a filter on your visual. You can then dynamically alter the number of distinct values as you feel like (4, 3, 2). 

I know this isn't quite as good as typing a formula into the visual filter field, though in practice it's still formula driven. The formula is just on an underlying table.

**Why so complex?**

The reason you have to do this for the Box & Whiskers visual, whereas your 'Distinct Count score' measure works so well, is that on the column chart, you are displaying a single value (the average score). The Box & Whiskers chart, by contrast, is plotting every individual score. 

In fact, if you removed the 'Course' from the axis of your column chart, the value changes as it adds back in the courses you filtered out. (The reason for this is that, if no course is on your axis, your formula calculates the distinct count of all the courses, which is 7). Likewise, if you were to filter your column chart to a particular session, your column chart would go blank (since in any given session, no course has more than 4 distinct values).

The technique I've described above fixes those problems, because it filters out the courses Math & English from the get-go. It doesn't matter if you've filtered to a single session, or not filtered by course at all. English & Math will always be excluded as long as their distinct count is below the value you specify.

Hope this helps.