Problem creating a DAX query

[tom_cowling]

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

[pedrovr2020]

Hello a serious choice like this

given

var ing = CALCULATE( [ValorDetalle] , 'Nominal'[Nominal] =1000)

var gast = CALCULATE( [ValorDetalle]*-1 , 'Nominal'[Nominal] =1100)

var pers = CALCULATE( [ValorDetalle]*-1 , 'Nominal'[Nominal] =1200)

return

ing+gast+pers

-------------------------------

data model

------------------------------

Result

[amitchandak]

@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?

[tom_cowling]

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

[Greg_Deckler · Accepted solution]

@tom_cowling - Try wrapping column references in something like MAX

[tom_cowling]

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.

[Greg_Deckler]

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