Forum Discussion
Cross Table If Statements?
Hi All,
Only started with PowerBI last week so go easy on me!
I have tables like the below added:
| Table A | |
| ID | Name |
| 1 | A |
| 2 | B |
| 3 | C |
| Table B | |
| ID | Model |
| 1 | 800 |
| 2 | HP 850 |
| 3 | Dell 900 |
| Table C | |
| ID | RAM |
| 1 | 8GB |
| 2 | 16GB |
| 3 | 4GB |
| Table D | |
| ID | HDD Type |
| 1 | HDD |
| 2 | SSD |
| 3 | Unknown |
There is a many to many relationship setup between A>BCD where BCD filter A. Tables B,C + D contain many more ID's than Table A.
I need to create a new column in Table A (I think?) that I can do some if filtering on across BCD. So for instance a couple variations would be:
IF ID= HDD=4GB Then "REPLACE"
IF ID= SSD=8GB Then "REUSE"
IF ID= SSD=8GB AND Model=800 Then "REPLACE"
etc
I cannot figure out how to do this! When I add a new column into A I dont seem to be able to bring in columns from the other tables.
Any help would be greatly appreciated!
Hi Anonymous
- Try Merge Queries like in the video below.
- Or if you wont to preserve the model and add columns from different tables use RELATED or RELATEDTABLE DAX functions.
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.- Try Merge Queries like in the video below.
4 Replies
Hi Anonymous
- Try Merge Queries like in the video below.
- Or if you wont to preserve the model and add columns from different tables use RELATED or RELATEDTABLE DAX functions.
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.Thanks Mariusz,
Related hasnt worked due to the relationships, and I'll be damned if I can get RelatedTable to work in the way I'm after.
For instance, if the below is a normal query, how would I turn this into using the RelatedTable? - where v_GS_DISK[Caption0groups] is the RelatedTable.
Support = IF(FIND("HDD",v_GS_DISK[Caption0groups], 1,BLANK()),"Replace")
Started looking at Merging. Its an option i'll continue to explore. A couple questions however;
1) Does the data in the merge update when the sources brings in new information?
2) Is it posible to merge a group? i.e in Table B I have a group i've made from a column. But when I merge Table B to Table A, its not an option. It took a reaaaly long time to make some of those groups and I reaaaly dont want to make them again manually in Table A post merge.
Thanks,
Rich
1) Yes, the merge is happening with each refresh and should normally be done in the beginning.
Extract->Transform->Load
2) because the merge is done in the beginning, you cannot use grouping or other columns you created after.
- Try Merge Queries like in the video below.
