Forum Discussion
Filter the calculated table
Hi everyone,
I have DAX problem. I'm tring to return only those rows from TABLE1 which have value in [Attribute] column is equal to TABLE2[name] value and TABLE1[project] = TABLE2[project].
Attributes =
VAR All_attributes =
SELECTCOLUMNS (
'TABLE1';
"casx_id"; TABLE1[id];
"Attribute"; TABLE1[name];
"project"; TABLE1[project];)
RETURN
FILTER (
All_attributes;
[Attribute] IN VALUES ( TABLE2[name] ) && [project] IN VALUES ( TABLE2[project] )) <-- it's not working
TABLE 1
| casx_id | Attribute | project |
| 1 | aaa | x |
| 2 | bbb | y |
| 3 | ccc | x |
TABLE 2
| name | project |
| aaa | x |
| bbb | y |
| ccc | z |
Returned table should look like this:
| casx_id | Attribute | project |
| 1 | aaa | x |
| 2 | bbb | y |
create a new column in table like
New column in Table 1 = maxx(filter(table2,table1[Attribute] = table2[name] && table1[project] = table2[project],table[name])
The rows you need is the one that has value
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
3 Replies
create a new column in table like
New column in Table 1 = maxx(filter(table2,table1[Attribute] = table2[name] && table1[project] = table2[project],table[name])
The rows you need is the one that has value
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners/ba-p/890814
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601amitchandak thank you for your answer ! It's working. But maybe you have idea how to put your formula:
maxx(filter(table2,table1[Attribute] = table2[name] && table1[project] = table2[project],table[name])
into my script which create new table (below I put simplified script, the orginal script have unions ect.):
Attributes =
VAR All_attributes =
SELECTCOLUMNS (
'TABLE1';
"casx_id"; TABLE1[id];
"Attribute"; TABLE1[name];
"project"; TABLE1[project];)RETURN
All_attributes
Anonymous
For your expected table, you should used equal "=" instead of "in values", and it is not necessary to created a temporary table.
Try the following calculated table.Table = CALCULATETABLE(VALUES(Table1),FILTER(Table1,[Attribute] = RELATED(Table2[name])&&[project]=RELATED(Table2[project])))Paul Zheng