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.
Hi Team,
Need help regarding how I can compare two tables having the same column name.
For example
Table 1- Last 3 days | Table 2- yesterdays | |||
Name | Rpc | Name | Rpc | |
Arvind | 1 | Arvind | 2.5 | |
Yadav | 0.23 | soumyadeep | 2 | |
Pushkal | 0.5 | Pushkal | 2.3 | |
Soumyadeep | 0.45 | Ashish | 1.5 | |
So I need collated form of a new table which provides me Table name Result i.e, In last 3 days and Yesterdays table which name exist in both tables which one is moved out and which is New coming into the pictures.
I need output as
Needed Table(From Table-1 & Table -2) | |
Name | Output |
Arvind | Exist |
Yadav | Move Out |
Pushkal | Exist |
Soumyadeep | Exist |
Ashish | New |
Thanks,
Arvind
Solved! Go to Solution.
Hey @arvindyadav
from my understanding the relationships of the model should look like this:
This will allow to create a report like this:
Please be aware that the columns Name and Output are used from the table New Table. The table "New Table" to filter both tables. I think it will a good idea to rename the column RPC accordingly to make it somewhat simpler to differentiate between both columns, eg. "RPC (...) Yesterday". Make sure that you use the "Name" column from the table "New Table".
Hopefully this is providing what you are looking for.
Regards,
Tom
Hey,
I created a new table "New Table" using this DAX statement:
New Table = DISTINCT( UNION( VALUES(Table1[Name]) ,VALUES('Table2'[Name]) ) )
Then I create a calculated column using this DAX statement:
Output = var thisName = 'New Table'[Name] var tExist = INTERSECT(VALUES('Table1'[Name]), VALUES('Table2'[Name])) var tMoveOut = EXCEPT(VALUES('Table1'[Name]), VALUES('Table2'[Name])) var tNew = EXCEPT(VALUES('Table2'[Name]), VALUES('Table1'[Name])) return SWITCH( TRUE() ,thisName in tExist, "Exist" ,thisName in tMoveOut, "Move Out" ,thisName in tNew, "New" ,"this is odd" )
After this the table New Table looks like this:
Hopefully this is what you are looking for.
Regards,
Tom
Hi @TomMartens ,
Thank You Very Much!!
It works but when I adding Table1 or table 2 RPC value then it reflects the same the number for all Name. How to fix this problem.
Thanks,
Arvind
hi, @arvindyadav
You just need to create the relationship between table1 and new table by Name column and create the relationship between table2 and new table by Name column too.
Best Regards,
Lin
Hi @v-lili6-msft ,
How to create a relationship between the table.
I tried but nothing happens.
Thanks,
Arvind
hi, @arvindyadav
you could change Cross filter direction is "Both" and try it again.
If still have the issue, could you please share your sample pbix file, there should be something wrong in other.
Best Regards,
Lin
Hi @v-lili6-msft ,
So basically I able to manage table1(Yesterdays) and Table2(Last 3 Days) tables relationship but when I try to relate New Table to any of the tables then I am getting a message like below:-
Hey @arvindyadav
from my understanding the relationships of the model should look like this:
This will allow to create a report like this:
Please be aware that the columns Name and Output are used from the table New Table. The table "New Table" to filter both tables. I think it will a good idea to rename the column RPC accordingly to make it somewhat simpler to differentiate between both columns, eg. "RPC (...) Yesterday". Make sure that you use the "Name" column from the table "New Table".
Hopefully this is providing what you are looking for.
Regards,
Tom
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 |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |