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.
Solved! Go to Solution.
Hello a serious choice like this
@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:
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."
Thank you both for the swift responses. I used @amitchandak's code with the MAX wrapping on columns and that seems to have worked.
@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.
Learn how to create your own user groups today!
Click here to read more about the November 2021 Updates!
Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.