Forum Discussion
Problem creating a DAX query
@tom_cowling - Try wrapping column references in something like MAX
tom_cowling , I do not see any syntax issue. This can be done like
SUMX(NOMINAL_INFO,Switch( true(),
NOMINAL_INFO[NOMGROUP]="INCOME" , DWH_FACT_MAIN[VALUE],
NOMINAL_INFO[NOMGROUP]="EXPENDITURE" ,-1* DWH_FACT_MAIN[VALUE],
NOMINAL_INFO[NOMGROUP]="STAFF",-1* DWH_FACT_MAIN[VALUE]))
Can you share sample data and sample output in table format?
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.
