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,
I need to create a table ,every row comes from different sources.
first row from cube1, second row from cube2 etc.
I created a custom table using UNION
Customers=UNION(table1,table2,table3,table4)
table1,2,3 were created as a custom tables using SUMMARIZE()
for example :
table1 = SUMMARIZE('Sales',column1, column2,sum(totalvalue)) - 'Sales' is a real cube
table 4 was created similer using SUMMARIZE BUT as a sourse table , I used a custom table from 2 joined tables.
I tried 2 ways to join the tables: 1) merge query, 2) dax naturalleftouterjoin
Result table 4 is not shown. No errors
Any ideas?
Solved! Go to Solution.
Hi @v-yiruan-msft thank you very much for your suggection. It was sorted. UNION did not work for me so I found the example in internet and it solved my problem
instead of using just Union (table1, table2,table3, table4)
I used Union(table21, table2,table3, SELECTCOLUMNS ( Table4, "Column1", Table4[Column1], "Column2", Table4[Column2], "Column3", Table4[Column3] ) and it works!!!!
)
Hi @liudmila ,
just out of curiosity, how many tables are "Calculated tables" in your model?
I would be interested to know why did you need to summarize data in a calculated table.
All of them are calculated tables: table1,table2 table3, table4
I use SUMMARIZE because I need to get sum of totalamount group by date, state etc. I found some examples how to to that using SUMMARIZE. So I created a new table with Date, State, SUM of total amount and then i can play with this table. (This table is not for visualization but for the futher manipulations)
Ok, I'm just asking because I've been doing some DAX for 5 years now and I can say that every person that I've seen used Summarize inside Calculated table didn't need to use it and it just took additional unnecessary memory inside their model. I'm not saying you are not using it properly, I'm just questioning the idea behind it.
Can you share with us a simple pbix file with data and visual you want to create?
Thank you for your suggestion, I don't know how i can avoid this step? Definitely I'd like to make the model using the best options but in internet I cannot find anything better.
I will create a simple it will take time 🙂 Thank you.
Exactly, of course, we can answer your original question related to Union but this won't solve the future issues you'll encounter if your Data model is not designed with best practices 🙂
@liudmila , can share how you created table4. if possible some sample data to create the same.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
It works perfectly in my example.
This is my example:
https://drive.google.com/file/d/1TAVWiVqS22K16hPGvk-o86aY-JrvUp6k/view?usp=sharing
Hi @liudmila ,
Please update the formula of calculated table AllDays_Tax as below:
AllDays_Tax =
VAR A =
SELECTCOLUMNS (
Flowers,
"Month_Year", Flowers[Date].[Month] & Flowers[Date].[Year],
"Date", Flowers[Date],
"Value", BLANK ()
)
VAR B =
SELECTCOLUMNS (
Tax,
"Month_Year", Tax[Date].[Month] & Tax[Date].[Year],
"Date", BLANK (),
"Value", Tax[Value]
)
VAR Result =
DISTINCT ( UNION ( FILTER ( A, NOT ( ISBLANK ( [Date] ) ) ), B ) )
RETURN
Result
If the above one is not working, could you please tell me what's your final expected result with the backend logic and function? I want to check whether we can achieve it only by creating measures or calculated columns... Thank you.
Best Regards
Hi @v-yiruan-msft thank you very much for your suggection. It was sorted. UNION did not work for me so I found the example in internet and it solved my problem
instead of using just Union (table1, table2,table3, table4)
I used Union(table21, table2,table3, SELECTCOLUMNS ( Table4, "Column1", Table4[Column1], "Column2", Table4[Column2], "Column3", Table4[Column3] ) and it works!!!!
)
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 |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |