cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tom_cowling
Frequent Visitor

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

 

1 ACCEPTED SOLUTION

@tom_cowling - Try wrapping column references in something like MAX


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

6 REPLIES 6
pedrovr2020
Frequent Visitor

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
datos.png
------------------------------

Result

imgayda.png

amitchandak
Super User
Super User

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Sample data below:

 

dwh_fact_main

AccountNominalValue
1234510006542
1234510002151
1234512006560
1234511008161
12345610001415
12345611005148
1234567100018411
1234567120011784

 

nominal_info

NominalNomgroup
1000Income
1100Expenditure
1200Staff

 

Expected output:

AccountTotal
12345-6,028
123456-3,733
12345676,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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

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
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.