Forum Discussion
DAX beginner question. Switch or IF
Here is an example of the data. This is the fact table, and every column except 'data' has a related dimension table.
| column | fund | dept_id | ACCOUNT | program | req_item | data |
| ADOPTED | 230 | 1015000100 | TOTEXP | 0 | 0 | 1053817 |
| ADOPTED | 230 | 1015000100 | TOTEXP | 0 | 101500_01 | 20000 |
| ADOPTED | 230 | 1015000100 | TOTEXP | 0 | 101500_02 | 0 |
| ADOPTED | 230 | 1015000100 | TOTEXP | 0 | 101500_03 | 5000 |
| ADOPTED | 230 | 1015000100 | TOTEXP | 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.