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.
Desire Result
Choose year from Slicer , return sum result of Scorecard .
Problems No single measure can be putted in scorecard and to apply for calculating all tables while year is selected by slicer .
e.g. sum(table20[Score]) sum(table21[Score]) sum(table22[Score])
Data
table20 YEAR GEO Type Score 2020 Asia A 1 2020 Africa A 2.5 2020 CentralAfrica A 0 2020 Europe A 0 2020 MiddleEast A 0 2020 America A 1.5 table21 YEAR GEO Type Score 2021 Asia A 3 2021 Africa A 2 2021 CentralAfrica A 6 2021 Europe A 1 2021 MiddleEast A 2 2021 America A 8 table22 YEAR GEO Type Score 2022 Asia A 4 2022 Africa A 0 2022 CentralAfrica C 3 2022 Europe C 4 2022 MiddleEast A 1 2022 America A 5
Relationship Calendar table link those tables .
Calander = CALENDAR(DATE(2020,1,1),DATE(2022,12,31))
What is the best way to achieve this without appends to be a larger dataset ?
Not sure "aggregation" based on "imported table" can help on the performance ? Please advise if my concept is basically wrong like append table cannot be avoided .e.t.c.
Thanks both Aburar_123 & m3tr01d comments . Going back to Azure with ETL approaches will be always my first perferences .If the situation that the constraint that I can only handle in Power Query , I have append & making key to restructure and long list Facttable (> 5 milions rows) . Do you think there is any funciton/setting like turning off reload of the full table everytime a perform the Key column making process to achieve a start schema ? Currently 1st ~5th Key column making cause > 5mins of loading in Powr Query , the 10th ~> 15th Key column making stucking more than 30mins....
Go to Power Query Editor then right click on the table and uncheck 'Include in report Refresh'.
See if this can improve your report performance.
@rane19
The best practice for the Datamodel is to have one Fact table and one dimension.
Storing it as table20, table21, table22 will be awfull for performance and it adds a lot of complexity to the measures you will create.
Follow @Aburar_123 advice and do proper ETL to store the data in one table in a SQL database.
Hi,
if you add it as a seperate table you need to create relation manually whenever you add new table.
If you append it everytime into a table that also will be a manual work.
These manual work would be painful for longer run.
So, my suggestion is to load these data into Azure sql server or On-prem sql server into a single table means append it every time using ADF or SSIS or any other ETL tool.
In this way you can reduce the manual work considerably and due to single table your report performance will be good due to the auto tuning by vertipaq engine.
If you dont want to use any ETL tools then my suggestion is to append the data into a single table and use it in Import mode.
Please like my post if it helps you. Thanks.
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 |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |