Measure where filtering is needed for all filtered data except for 1 filtering

[Anonymous]

Hello,

We are using Row Level Security with Direct Queries to a SQL database.
For one KPI, I want to create a measure that should use all the filtered data except for 1 applied filter.

I have 3 tables.
Table: Status

StatusId (Int)	StatusTenant (RLS)	Status
1	TenantA	Submission
2	TenantA	Bound
3	TenantA	Rejected

Table: Year

Year (Int)	StatusTenant (RLS)
1999	TenantA
2000	TenantA
2020	TenantA

Table: Industry

IndustryId (Int)	IndustryTenant (RLS)	Industry
1	TenantA	Industry A
2	TenantA	Industry B
3	TenantA	Industry C

Table: Data

Id (Int)	Tenant (RLS)	IndustryId (Int)	StatusId (Int)	Value (Decimal)	Year
123	TenantA	1	1	10	1999
456	TenantA	2	2	100	2000
789	TenantA	1	2	200	2000

There are Many-to-Many relationships between:
Industry[IndustryId] and Data[IndustryId]
StatusId[StatusId] and Data[StatusId]

There are 3 slicers on the report:
Slicer Industry: filters based on the Industry (empty & multiselect are possible)

Slicer Status: filters based on the Status (empty & multiselect are possible)

Slicer Date: Start & End date which filters the data on Year (for the KPI's this users the FILTER function)

I have several KPI's based on the filtered data set through the slicers without any issue but now I need to have a new KPI which would need all the filtered data but without the filter StatusId applied.

ex:
Slicer Industry: Industry A & Industry B are selected
Slicer Status: Submission
Slicer Date: Between 1980 and 2030

Filtered data would then be

Id (Int)	Tenant (RLS)	IndustryId (Int)	StatusId (Int)	Value (Decimal)	Year
123	TenantA	1	1	10	1999

COUNT = 1

For this count, I would actually need

Id (Int)	Tenant (RLS)	IndustryId (Int)	StatusId (Int)	Value (Decimal)	Year
123	TenantA	1	1	10	1999
456	TenantA	2	2	100	2000
789	TenantA	1	2	200	2000

Expected result of the COUNT would be 3 but I get a COUNT of 1 since all filters stay applied.

I've already tried several approaches (ALL, ALLEXCEPT, KEEPFILTERS, REMOVEFILTERS) but none seems to be working.
or no filter is applied or all filters keep applied.

I'm wondering if it's possible with RLS & Direct Query to achieve this.
I hope someone sees a solution.

Thanks in advance,
David

[PaulDBrown]

It may be because you have many-to-many relationships

[v-jingzhang]

Hi @Anonymous 

Try this measure

Count =
CALCULATE (
    COUNT ( 'Data'[Id] ),
    ALLEXCEPT ( 'Data', 'Data'[IndustryId], 'Data'[Year] )
)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anonymous]

Hello @v-jingzhang ,

thanks for the reply, but I tried it this already and to be sure tried it again but I keep getting the same result.
A Count of the Data gives the same result

Measure =

VAR __Total1 = COUNT(Data[Id])

VAR __Total2 = CALCULATE (COUNT ( 'Data'[Id] ), ALLEXCEPT ( 'Data', 'Data'[IndustryId]))

RETURN CONCATENATE(CONCATENATE("Total 1: ", __Total1), CONCATENATE(" Total 2: ", __Total2))

If the Status = "BOUND" it gives the result 33 for both totals. If I remove the Status "BOUND" to nothing, so no filtering applied, it gives a result of 56000 rows.
I would expect with the Status Filtered on "BOUND" that __Total1 = 33, but __Total2 = 56000.

I'm not sure if this could be related to the Row-Level-Security or the fact that I'm using the DirectQuery approach.