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 Expert
I have blow scenerio.
I don’t want to use merge table or create new table using create table option or NATURALLEFTOUTERJOIN(as it will create new table). Is any Dax(measure or Dax column) can meet my purpose.
Solved! Go to Solution.
I have got solution from Microsoft person and i understood that bydefault whenever we create a relationship, Power BI implment INNER JOIN by default however the values are in the table, but are hidden by default since Power BI only shows accounts that have any transactions. To include empty accounts as well, change the Account attribute binding to “Show items with no data"
AND This worked for me. sometime easy solution of difficult issues.
I have got solution from Microsoft person and i understood that bydefault whenever we create a relationship, Power BI implment INNER JOIN by default however the values are in the table, but are hidden by default since Power BI only shows accounts that have any transactions. To include empty accounts as well, change the Account attribute binding to “Show items with no data"
AND This worked for me. sometime easy solution of difficult issues.
hi @deepakb18
Just rename one of the [Account] column eg. one is [Account] and another one is [Account 1].
Then use NATURALLEFTOUTERJOIN Function as below:
New Table = NATURALLEFTOUTERJOIN(Account,'Transaction')
Or adjust it as below:
New Table =
--NATURALLEFTOUTERJOIN(Account,'Transaction')
SELECTCOLUMNS(NATURALLEFTOUTERJOIN(Account,'Transaction'),"Account",[Account 1],"Desc",[Desc],"Transld",[Transld],"DebitAmount",[DebitAmount],"CreditAmount",[CreditAmount])
Result:
Regards,
Lin
Hi Lin
Thanks for your reply but i dont want to create a new table using Natural Joins as data model is alredy heavy and it took longer time for pulling data and data model to get refresh.
Hi @deepakb18 ,
If you want 3rd table (as in your screenshot) just a visual in Power BI, then you can create a relationship between both of o your tables - Account, Transaction on "Account" column whihc is a common column in both.
Then create a table visual in your Power BI report, moving all the relevant fields, as in your third table. You will get desired output.
If this helps please give Kudos and mark it as a Solution! 🙂
Thanks,
Pragati
Well relationship is already there
Hi @deepakb18 ,
then try creating the table visual on your report (like your 3rd table in the screenshot).
If this helps please give Kudos and mark it as a Solution! 🙂
Thanks,
Pragati
You should refer this and try in Edit Queries
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
You can use Cross Join in Dax but that is costly
filter(Crossjoin(Table1,table2),conditions)
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 |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |