Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
)
)
)
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.
@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
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |