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
uBoatCaptain
Helper I
Helper I

DAX beginner question. Switch or IF

Hello. Pardon the ignorance embedded in this question, but I am trying to put together a proof of concept report and I have a measure that is critical. I believe DAX can compute this but as yet I lack the depth to troubleshoot the logic and/or syntax issues I am having with this measure.

 

I have a fact table (CFNDFact) that has columns for chart of account fields and a single value row called "data". I need a measure that, based on the value of the "ACCOUNT" field (of which there are only four), will return either 0, the value of [data], or the negative of [data]. I want this measure computed for each row and the summary groupings to be handled by the visualizations.

 

I am sure it is just my ignorance of some basic DAX that is making this much harder than it is. I hope one of you experienced people can spare a couple minutes to point out what I don't get yet. Thanks for your help.

 

A couple things I have tried are:

 

County Funding = SWITCH([Account],
"INTREV",0,
"NONOPREV",0,
"OPREV",-[data],
"TOTEXP",[data],0)

 

County Funding =
IF ([ACCOUNT]="INTREV",0,
IF ([ACCOUNT]="NONOPREV",0,
IF ([ACCOUNT]="TOTEXP",[data],
IF ([ACCOUNT]="OPREV",-[data],))))

4 REPLIES 4
achinm45
Advocate IV
Advocate IV

Hi @uBoatCaptain,

Can you post sample data on which you are trying to apply the formulas ?

 

Thanks

Achin

Here is an example of the data. This is the fact table, and every column except 'data' has a related dimension table.

 

columnfunddept_idACCOUNTprogramreq_itemdata
ADOPTED   230       1015000100TOTEXP    0         0         1053817
ADOPTED   230       1015000100TOTEXP    0         101500_01 20000
ADOPTED   230       1015000100TOTEXP    0         101500_02 0
ADOPTED   230       1015000100TOTEXP    0         101500_03 5000
ADOPTED   230       1015000100TOTEXP    0         101500_05 5000

 

I attempted to see if I could create this as a column and it worked. I simplified the formula in the process to County Funding = SWITCH(CFNDFact[ACCOUNT],"TOTEXP",[data],"OPREV", -[data],0).

 

Trying to use this same formula (with a different measure name) for a measure gets the error

'A single value for column 'data' in table 'CFNDFact' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.'

 

@uBoatCaptain - Glad you got it working. Yep, that was what I figured was going on and the exact error that I would have expected you to get with that formula used in a measure. Basically, a measure has to use an aggregation function because its context is dynamic within a report depending upon filters, slicers and the visualizations themselves that they are used within. Therefore, you can't single out any particular thing with a measure, measures always assume that they are dealing with a "set" of things. Always think of measures as if you have to feed it a "table" of things (multiple rows). Calculated columns on the other hand are constrained to their row context (more or less) and so you can grab individual values in that row or a related row in another table.


@ 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...
Greg_Deckler
Super User
Super User

Sounds like this should be a column really rather than a measure potentially. And I'm not sure of the logic for summary groupings honestly. What is the problem you are running into? Syntax error or something else?


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