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...
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