Forum Discussion
Problem creating a DAX query
@tom_cowling - Try wrapping column references in something like MAX
Sample data below:
dwh_fact_main
| Account | Nominal | Value |
| 12345 | 1000 | 6542 |
| 12345 | 1000 | 2151 |
| 12345 | 1200 | 6560 |
| 12345 | 1100 | 8161 |
| 123456 | 1000 | 1415 |
| 123456 | 1100 | 5148 |
| 1234567 | 1000 | 18411 |
| 1234567 | 1200 | 11784 |
nominal_info
| Nominal | Nomgroup |
| 1000 | Income |
| 1100 | Expenditure |
| 1200 | Staff |
Expected output:
| Account | Total |
| 12345 | -6,028 |
| 123456 | -3,733 |
| 1234567 | 6,627 |
The error I am receiving when I use both my query and amitchandak's is the following:
"Semantic error: The value for column 'Value' in Table 'DWH_FACT_MAIN' cannot be determined in the current context. Check that all columns in referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation -- such as sum, average, or count-- on that column. The column does not have a single value, it has many values, one for each row in the table, and now row has been specified."
@tom_cowling - Try wrapping column references in something like MAX
Brilliant!
Thank you both for the swift responses. I used @amitchandak's code with the MAX wrapping on columns and that seems to have worked.