Forum Discussion
DAX Calculation: multiple tables
Hi Fabric Community,
I am new to Power BI and facing issues to create a dax calculation to achieve the following result:
From table 1 for insitution ABC, id 10, header string='A' we need to pick out responsecount=14012333 and divide it by values from the table 2 with following inputs institution ABC, Sum of values for Optionstring= 'Direct Costs' and 'Indirect Costs" and headerstring='aa' and 'bb' which means= 46 dollars( 13+16+4+13) refered filtered table 2
I am looking for the values of 1401233/46.
Table1:
Table 2:
Filtered Table2:
Thanks
Ekam
5 Replies
Hello esingh
As per your explanation & requirement you can try the below DAX but There might be a problem afterwards cause as you explained all the filter values are hardcoded in to the DAX.
It Means This DAX only work for mentioned filters It means the values are specified.
Thanks & Regards...Tanks Biswajit , But the data need to vary based on the insitute. filter remain fixed.
Ekam
pls paste the sample data here (not the screenshot). What about the rest ,e.g. DEF? also get the value from table 2 with only "aa" and "bb" in the Direct Costs' and 'Indirect Cost"?
Hi Ryan Mayu, You are right in your interpretation we need to get the data for DEF as well. I have just kept data of two institutes in table 2 for the purpose. Filter values remain unchanged, institute changes(dynamic). Sample data.
id optionString headerString responseCount institution_name institution_code 10 xx A 1401233 ABC 10 10 xx A 1401000 DEF 20 10 yy A 0 GHI 30 10 yy A 0 JKL 40 10 xx B 59 ABC 10 10 xx B 53 DEF 20 10 yy B 0 GHI 30 10 yy B 0 JKL 40 Table 2: id optionString headerString responseCount institution_name institution_code 4 Expenditures aa $15 ABC 10 4 Expenditures bb $18 ABC 10 4 Expenditures cc $12 ABC 10 4 Direct Costs aa $13 ABC 10 4 Direct Costs bb $16 ABC 10 4 Direct Costs cc $11 ABC 10 4 Indirect Costs aa $4 ABC 10 4 Indirect Costs bb $13 ABC 10 4 Indirect Costs cc $2 ABC 10 4 Assets aa $10 ABC 10 4 Assets bb $12 ABC 10 4 Assets cc $8 ABC 10 4 Labor Expense aa $20 ABC 10 4 Labor Expense bb $64 ABC 10 4 Labor Expense cc $13 ABC 10 4 Expenditures aa $15 DEF 20 4 Expenditures bb $18 DEF 20 4 Expenditures cc $12 DEF 20 4 Direct Costs aa $13 DEF 20 4 Direct Costs bb $16 DEF 20 4 Direct Costs cc $11 DEF 20 4 Indirect Costs aa $4 DEF 20 4 Indirect Costs bb $13 DEF 20 4 Indirect Costs cc $2 DEF 20 4 Assets aa $10 DEF 20 4 Assets bb $12 DEF 20 4 Assets cc $8 DEF 20 4 Labor Expense aa $20 DEF 20 4 Labor Expense bb $64 DEF 20 4 Labor Expense cc $13 DEF 20 pls try this
Measure = DIVIDE(sum('Table'[responseCount]), SUMX(FILTER('Table (2)','Table (2)'[institution_name]=MAX('Table'[institution_name])&& 'Table (2)'[optionString] in {"Indirect Costs","Direct Costs"} && 'Table (2)'[headerString] in {"aa","bb"}),'Table (2)'[responseCount]))pls see the attachment below
