Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
@tom_cowling - Try wrapping column references in something like MAX
Hello a serious choice like this
Result
@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.
@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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |