Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello everyone,
I have PowerBI report (not created by me, just inherited) that I need to optimise memory consuption. Report consist of more number of companies. Each company can have different ERP (5 different ERPs in group), and different source SQL databse, Sharepoint, Excel etc.
Report is created with this logic. Each company have this kind of DAX measure that react on user slicer in report:
PL_companyxyz =
VAR _PL1 = SUM([PL1])
VAR _PL2 = SUM([PL2])
VAR _PL3 = SUM([PL3])
VAR _PL4 = SUM([PL4])
RETURN
SWITCH( SELECTEDVALUE(Slicer_data[data1]) ,
"PL1" , _PL1 ,
"PL2" , _PL2,
"PL3", _PL3,
"PL4" , _PL4 , _PL1)
Each PL column contains the type of accounting entries.
In the report there is a consolidation DAX that binds each company's data together:
PL_all_companies =
var XYZ = IF( CONTAINS(Group , Group[Company] , "XYZ" ) , [PL_companyxyz] , BLANK())
var XYZ2 = IF( CONTAINS(Group , Group[Company] , "XYZ2" ) , [PL_companyxyz2] , BLANK())
var XYZ3 = IF( CONTAINS(Group , Group[Company] , "XYZ3" ) , [PL_companyxyz3] , BLANK())......etc
var all_companies = XYZ + XYZ2 + XYZ3 + ..... etc
RETURN
all_companies
If statement is controlled by another slicer on page.
Is there a better way or best practice how to bind together all companies data, using DAX?
Thanks in advance,
Kind regards.
Hello,
If it is possible, try to unify data from all the companies into one data table to void calculating the total summing results from different measures.
Regards
Hello,
Thank you for your reply. I cannot do that because there are 5 different ERPs in group. I may be able to put them in ERP groups, but I would prefer the DAX solution if it is possible to write it in a better or more effective way.
Kind regards.
User | Count |
---|---|
106 | |
89 | |
82 | |
76 | |
73 |
User | Count |
---|---|
112 | |
103 | |
96 | |
74 | |
67 |