Forum Discussion
Problem creating a DAX query
Hi,
I'm really struggling to get the syntax right on this DAX query I'm writing and I can't seem to find a solution that fits online.
I have two tables. Dwh_Fact_Main and Nominal_Info.
Fact_Main has columns called Nominal and Value. Nominal_Info has Nominal and nomgroup (which contains a few different values I want to filter on).
My query in plain English would be:
Do a SUM of Value on all rows in fact_main with the nomgroup of "Income", minus the SUM of Value on all rows with the nomgroup of "Expenditure", minus the SUM of Value on all rows with the nomgroup of "Staff".
What I have so far is the following, but it's no good.
SUMX(FILTER(NOMINAL_INFO, NOMINAL_INFO[NOMGROUP]="INCOME"),DWH_FACT_MAIN[VALUE])- SUMX(FILTER(NOMINAL_INFO, NOMINAL_INFO[NOMGROUP]="EXPENDITURE"),DWH_FACT_MAIN[VALUE])- SUMX(FILTER(NOMINAL_INFO, NOMINAL_INFO[NOMGROUP]="STAFF"),DWH_FACT_MAIN[VALUE])
Any help would be much appreciated.
Thanks,
Tom
@tom_cowling - Try wrapping column references in something like MAX
6 Replies
tom_cowling - Are you getting a syntax error? Hard to troubleshoot otherwise without sample source date. Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.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
Hello a serious choice like this
givenvar ing = CALCULATE( [ValorDetalle] , 'Nominal'[Nominal] =1000)var gast = CALCULATE( [ValorDetalle]*-1 , 'Nominal'[Nominal] =1100)var pers = CALCULATE( [ValorDetalle]*-1 , 'Nominal'[Nominal] =1200)returning+gast+pers-------------------------------data model------------------------------Result
