Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

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

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
1TenantASubmission
2TenantABound
3TenantARejected

Table: Year

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

Table: Industry

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

 

Table: Data

Id (Int)Tenant (RLS)IndustryId (Int)StatusId (Int)Value (Decimal)Year
123TenantA11101999
456TenantA221002000
789TenantA122002000

 

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
123TenantA11101999

COUNT = 1

For this count, I would actually need

Id (Int)Tenant (RLS)IndustryId (Int)StatusId (Int)Value (Decimal)Year
123TenantA11101999
456TenantA221002000
789TenantA122002000

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

3 REPLIES 3
PaulDBrown
Community Champion
Community Champion

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






v-jingzhang
Community Support
Community Support

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
Not applicable

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.