Forum Discussion

BenHoward's avatar
BenHoward
Icon for Helper I rankHelper I
5 years ago

create new table using a measure as a filter

I have two tables Table 1 and Table2 which are unrelated, and by using a measure which I build up from a selection of values from Table1, I want to build a 3rd table which is a subset, ie a filter, of Table2.

 

Here's the measure I create from Table1. (you can ignore the hardcoded result R2 for now)

Selected name = 
VAR R1 = SELECTEDVALUE(Table1[Column1])
VAR R2 = "Rashi"
RETURN
    R1

 

Then I create Table3 from Table2

Table3 = FILTER(Table2, Table2[Name] = Table1[Selected name])

 

This results in Table3 with zero rows.  

 

However, if I change my measure to use the hardcoded result, eg

Selected name = 
VAR R1 = SELECTEDVALUE(Table1[Column1])
VAR R2 = "Rashi"
RETURN
    R2

then Table3 is created with a single row, which is what I want.  

 

How do I go about creating the table using the variable R1 in the measure, what is the difference between returning the values R1 and R2 - I've done some tests and can see no difference.  An example PBI file is attached here.  

 

Thanks in advance.

 

5 Replies

    • BenHoward's avatar
      BenHoward
      Icon for Helper I rankHelper I

      amitchandak - the table is created when using the measure as a filter, it contains data when the measure is written as 

      SelectedName = 
      VAR R1 = "Rishi"
      RETURN R1

      but not when the measure is written as 

      VAR R1 = SELECTEDVALUE(Table1[Column1])
      RETURN R1

      I'm trying to understand why this would be.

  • Anonymous's avatar
    Anonymous
    Not applicable

    In the first line, you said Table1 & Table2 are unrelated. But still trying to filter the values of Table2 based on Table1.

    Can you share sample data for the tables that you have and the output you are expecting?