How to dynamically mask data?

[EricMamet]

In our database, we have a "Secret" boolean flag on each fact row.

Whenever I try to report on our data, I cannot return a member as a separate named entity if all the underneath leaf rows are marked with "Secret". In that case, I must group the member value with a real or pretend member "Others".

In the screenshot below I show the expected results depending on the queries

I understand in each request, I would have to invent an "Others" member if it does not exist and assign it the value that are "secret" in this context.

Any hint how I could go about doing this?

 Thanks

PS: Just in case you want to "practice"...

CREATE TABLE dbo.Fact( Colour VARCHAR(100) NOT NULL
, Shape VARCHAR(100) NOT NULL
, Size VARCHAR(100) NOT NULL
, Secret BIT NOT NULL
, Qty INTEGER NOT NULL
, PRIMARY KEY(Colour, Shape, Size)
);
GO

INSERT INTO dbo.Fact(Colour, Shape, Size, Secret, Qty)
VALUES ('Blue', 'Square', 'Big', 0, 1)
,('Green', 'Round', 'Big', 0, 3)
,('Green', 'Square', 'Small', 1, 2)
,('Blue', 'Triangle', 'Big', 0, 5)
,('Red', 'Round', 'Small', 1, 2)
,('Red', 'Square', 'Small', 1, 7)
,('Red', 'Square', 'Big', 0, 4)

[amitchandak]

@EricMamet , refer if these can help

https://www.biinsight.com/power-bi-desktop-query-parameters-part2-dynamic-data-masking-and-query-par...

https://radacad.com/secure-the-sensitive-data-in-power-bi-data-masking-better-with-row-level-securit...

[EricMamet]

Not directly but the second article (radacar...) has an idea that might be useful: splitting the original table into two.

I could split the "Secret" rows from the non secret ones... although I don't have any idea what to do next !!! 😂

My difficulty is that we need to hide column members dynamically depending on the presence or not of "non secret" data underneath and reporting everything hidden under an "Others" member that may or may not exist as well.

Finding out whether the numbers should be displayed normally is the easy bit I suspect...