Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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?

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.