Filter w/ disconnected table

DAX
#1

Hi @sam,
I want to create a Query By Example report that let users query data using filters selecting values from slicers and choosing the and/or logic.

I’m stuck since I cannot use the selected values of a slicer using the TREATAS to filter the fact table.
I’ve used the TREATAS many times, but never in a case like this one, where one of the parameters is a variable.
How can I achieve that?

Thanks for your help

Roberto

WA_Fn-UseC_-Telco-Customer-Churn.zip (440.7 KB)

image
image1076×688 11.5 KB

#2

you need to create a filter measure and add it to the filter pane for the visual:

eDNA Filter = 
VAR _Selection = SELECTEDVALUE( PaymentMethods[PaymentMethod] )
VAR _RowValue = SELECTEDVALUE( 'WA_Fn-UseC_-Telco-Customer-Churn'[PaymentMethod] )
RETURN
    IF( _Selection = _RowValue, 1, 0 )

image

image

in the attached, I have duplicated your visual, and have one that shows the filter measure, and one that only uses it on the filter pane.
eDNA Solution - selection disconncted table.pbix (273.3 KB)

1 Like
#3

Hi @Heather ,
thanks for your solution.

I was wondering if I could avoid using filters in the UI and solve this in DAX.
What I was thinking is using the selected values in the slicer to intersect with the proper column in the fact table, but I cannot get a table to use as a parameter in INTERSECT or TREATAS from the

Result =
CALCULATE(
[Measure],
TREATAS(
ALLSELECTED( DisconnectedTable[Column] ), << this of course doesn’t work
FactTable[Column]
)
)

#4

Hi @Heather ,
it was easier than I thought.

Test =
VAR SelectedValues = ALLSELECTED( DisconnectedTable[Column] )
RETURN

CALCULATE(
[Measure],
filter(FactTable, FactTable[Column] in SelectedValues )
)

Thanks!

Roberto

1 Like