Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
See there're 2 tables Table 1 & Table 2.
I need to get the sum for row values of table 1 from columns of table 2.
Hi @Anonymous ,
According to your description, here's my solution.
1.Create relationship between the two tables.
2.Create a measure in the table1.
Total Sum =
SWITCH (
MAX ( 'Table1'[Type] ),
"SalesA", MAX ( 'Table2'[SalesA] ),
"SalesB", MAX ( 'Table2'[SalesB] ),
"SalesC", MAX ( 'Table2'[SalesC] ),
"SalesD", MAX ( 'Table2'[SalesD] ),
"SalesE", MAX ( 'Table2'[SalesE] ),
"SalesF", MAX ( 'Table2'[SalesF] )
)
3.Put Range of table2 , Type of table and the measure into the table visual, get the expected result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous
First, you need unpivot our Table 2 so you will have three columns as you need in Power Query:
https://www.youtube.com/watch?v=Vff2kRBM95o
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
I already tried unpivoting the columns but it would increase the loading time way more for the data is more then 100Mn rows. if i don't unpivot the loading time is 2 minutes approx but if i do the loading time jumps to 1hr.
@Anonymous
I would still advise you to re-organize your data as you have a large amount. You have it done at the source level. You can request your data source owner to do it for you.
Doing it dax is painful and will cause memory and performance issues:
However, You can try this approach with DAX with UNION and SELECTCOLUMNS : https://stackoverflow.com/questions/50213905/is-it-possible-to-unpivot-in-power-bi-using-dax
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
I have managed to do so using if sum & calculate but the result is constant and would not change as per any applied slicer but thanks a lot for reaching out (Y)
User | Count |
---|---|
88 | |
84 | |
65 | |
62 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
82 | |
71 |