Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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?
The link to the example file is https://onedrive.live.com/?authkey=%21AO78dbRcuIQICFU&cid=00C18FDBA008D584&id=C18FDBA008D584%2123585...
<Update>
After a few hours work I have managed to create the table I needed using Power Query
</>
Alas not, the example I supplied is a much simpler version of the use case where I create a measure based upon a selection from 2 unrelated tables, which then provides the input for the filter which is applied to a third unrelated table.
@BenHoward , I doubt you can create a Table using the measure as a filter.
Try using summarizecolumns
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |