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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Optimization of Filtered Scenarios with a Disconnected Table

Hello,

 

I have a disconnected table that acts as my slicer/filter.  It is intended to interact with a related DIM table/field.  This related DIM table/field may not have records for all FACT table records I am calculating measures on.

Using ALLSELECTED (instead of SELECTEDVALUE) from the disconnected table is not adequate because results only include FACT records with related DIM table records, excluding FACT records with no associated DIM table records.

I want to filter my FACT table if there is a selection, but ignore the disconnected table entirely if nothing is selected.

I have a working approach shared below, but the performance is now an issue (I have 10+ metrics using parallel logic).
Is there a more optimal way of filtering based on a disconnected table, but only if there is a selection?

 

Bonus points if you can also help proactively address possible '(Blank)' measure results without further hampering performance.  Deriving (Blank) as 0 would be fine.

 

Thank you.

 

Joe

Disconnected table = selectedViolation
FACT table = Data_ACTUAL
DIM Table = Violation Detail

 

Shift Hours =

VAR selectedViol = SELECTEDVALUE( selectedViolation[Violation Category] )

RETURN
IF(
ISBLANK( selectedViol ) ,
CALCULATE(
SUM( Data_ACTUAL[Shift Hours] )
) ,
CALCULATE(
SUM( Data_ACTUAL[Shift Hours] ) ,
FILTER(
'Violation Detail' ,
'Violation Detail'[Violation Category] = selectedViol
)
)
)

 

2 REPLIES 2
Jihwan_Kim
Super User
Super User

Hi, @Anonymous 

I am not quite sure about the below measure, because I could not test it.

Could you please try the below?

 

Shift Hours =
VAR selectedViol =
ALLSELECTED ( selectedViolation[Violation Category] )
RETURN
IF (
NOT ISFILTERED ( selectedViolation[Violation Category] ),
CALCULATE ( SUM ( Data_ACTUAL[Shift Hours] ) ),
CALCULATE (
SUM ( Data_ACTUAL[Shift Hours] ),
FILTER (
'Violation Detail',
'Violation Detail'[Violation Category] in selectedViol
)
)
)

 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Anonymous
Not applicable

@Jihwan_KimThank you for your reply.

 

Both of our solutions use a conditional If Statement to calculate, either with or without a filter.  I suspect the performance issues is a byproduct of introducing the SUM function and its row-by-row calculation.

 

Unless there is more to the functions you chose to use, I see our two approaches merely as alternatives, with the same performance challenge.

Do NOT IS FILTERED and ALLSELECTED introduce efficiencies over my approach?

 

Please let me know if there was more to you approach than I am aware.

 

Thank you,

 

Joe

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors