How to filter related table based on an inactive relationship

[powerx786]

Hi, I have two tables with an active and inactive relationship between them. 

The active relationship is from Table4[Id] <->Table5[Number]

The inactive relationship is from Table4[Name] <->Table5[Name] 

These are the values

I want to generate the CalculatedTable based on the row selected based on Name column in Table4. 

StaticFilteredTable has this code which is not what I want as this does not get modified by Table4 selction:

StaticFiltered = FILTER(Table5,CONTAINS(Table4,Table4[Name],Table5[Name]))

This is the code for CalculatedTable but it is always giving me a blank result as shown below:

CalculatedTable =

VAR SELECTED = SELECTEDVALUE(Table4[Name], "not selected")

RETURN FILTER(Table5,Table5[Name]=SELECTED)

Any ideas on how to generate the filtered table based on dynamic slection from Table4?

[Anonymous · Accepted solution]

Hi @powerx786 ,

According to my understanding, you want to filter StaticFiltered table based on selected Name in Table4, right?

You could use the following formula:

Measure =
VAR _SELECT =
    SELECTEDVALUE ( Table4[Name] )
RETURN
    IF ( SELECTEDVALUE ( StaticFiltered[Name] ) = _SELECT, 1, 0 )

Then apply the measure(set as “is 1”) to the visual filter. My visualization looks like this:

Is the result what you want? If you have any questions, please upload some data samples and expected output.

Please do mask sensitive data before uploading.

Best Regards,

Eyelyn Qin

[powerx786]

Hi @Anonymous , yes! this result you showed is what I want. 

Can you expand on "Then apply the measure(set as “is 1”) to the visual filter.".

How would I apply the measure (with values 0 or 1) to the visual ?

Thanks!

[Anonymous]

@powerx786 ,like this:

Click “Apply filter” ,after click a Name in Table4, the visual will be right.

Best regards,

Eyelyn Qin 

[powerx786]

Thanks @Anonymous ! 

I modified the measure definition a little bit to meet my need, which seems to work well and filters StaticFiltered table when a row from Table4 is selected (both dont need to be selected).

Measure =

VAR _SELECT =

    SELECTEDVALUE ( Table4[Name] , "NaN")

RETURN

    IF ( CONTAINS(StaticFiltered, StaticFiltered[Name], _SELECT) || _SELECT="NaN", 1, 0 )

Then I had to add the measure as one of the 'Values' in the table visual and then I was able to filter the visual by the measure. The only drawback seems to be that the measure gets added as a column in the table visual. I wonder if there is a way to hide it from appearing in the visual. Other than that, it seems to work great.

Thanks!

[Anonymous]

Hi @powerx786 ,

For my test, just put the measure into the filter pane, it does not need to be put into the table as a Value.

Could you upload a sample pbix file?

Best regards,

Eyelyn Qin

[powerx786]

Hi @Anonymous yes it works like you mentioned. The meaure needs to be dragged into the filter pane.

Thanks!

[Fowmy]

@powerx786 

Creating a dynamic table based on a selection from a slicer value?
Calculated tables get materialized at the time of creating, refreshing, and loading them. Any selection from canvas cannot impact them. Am I missing something here?

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

  

[powerx786]

Ok @Fowmy  then is there any way to solve this issue?

[Greg_Deckler]

@powerx786 - Use USERELATIONSHIP.

[powerx786]

@Greg_Deckler  I have found USERELATIONSHIP works with measures but not for filtering a table. Maybe I am doing something wrong .. (?)

This is the calculation I tried but it does not filter however when I select a row in Table4

TableByUseRelationship = CALCULATETABLE(Table5, USERELATIONSHIP(Table5[Name], Table4[Name]) )

[Greg_Deckler]

@powerx786 - I'm not sure that USERELATIONSHIP is a valid filter context for CALCULATETABLE.