cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ph
Helper I
Helper I

get value from table with 2 conditions

Hi all !

 

I have problem with unfriendly DAX 😞 I need to get this value (written in T-SQL):

 

select [Amount]

from [Account Balance]

where left(account,3)=211 and date=today()

 

so I wrote this DAX formula:

Acc211 = calculate(
                                 sumx(
                                            'Account Balance'; 'Account Balance'[Amount]);
                                             AND(
                                                      left(Account[Accounting Class]; 3) = "211";
                                                      'Date'[Date ID]=[Today formatted]
                                              )
                                   )
 
*Today formatted is TODAY converted on the INT and is OK (validated)
 
I got error message: 
 "The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression."


can you please help me to fix it, and explain why and where is the mistake?

many thanks !!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Mate, DAX is simple but not easy - let that sink in. I'll give you good advice: get yourself a good book on DAX and read. The best book by far is "The Ultimate Guide To DAX" by The Italians.

 

Second, filters in CALCULATE are always tables. Even though you can write

 

T[Col] = "value"

 

it's always expanded into

 

FILTER(

    ALL ( T[Col] ),

    T[Col] = "value"

)

 

It means, among others, that you can't use two different columns in such a boolean expression. And this is what you're trying to do.

 

Secondly, don't do this: left(Account[Accounting Class]; 3). Please create a calculated column with this formula in it.

 

Thirdly, then you'll be able to do this:

 

Acc 211 =
var __today = [Today formatted]
var __acc211 =
	CALCULATE(
		SUM( 'Account Balance'[Amount] ),
		'Date'[Date ID] = __today,
		Account[Accounting Class 3] = "211" -- this is the new calculated column (hidden)
	)
return
	__acc211

 

Easy? That's what I thought 🙂

 

Best

Darek

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Mate, DAX is simple but not easy - let that sink in. I'll give you good advice: get yourself a good book on DAX and read. The best book by far is "The Ultimate Guide To DAX" by The Italians.

 

Second, filters in CALCULATE are always tables. Even though you can write

 

T[Col] = "value"

 

it's always expanded into

 

FILTER(

    ALL ( T[Col] ),

    T[Col] = "value"

)

 

It means, among others, that you can't use two different columns in such a boolean expression. And this is what you're trying to do.

 

Secondly, don't do this: left(Account[Accounting Class]; 3). Please create a calculated column with this formula in it.

 

Thirdly, then you'll be able to do this:

 

Acc 211 =
var __today = [Today formatted]
var __acc211 =
	CALCULATE(
		SUM( 'Account Balance'[Amount] ),
		'Date'[Date ID] = __today,
		Account[Accounting Class 3] = "211" -- this is the new calculated column (hidden)
	)
return
	__acc211

 

Easy? That's what I thought 🙂

 

Best

Darek

View solution in original post

Thanks Darek for response 🙂

 

1) thx for tip, I find only book called 'The Definitive Guide to DAX: Business Intelligence with Microsoft Excel, SQL ...' did you mean this? 🙂

 

2) Why I should  avoid to 'left(Account[Accounting Class]; 3)', I agree that code can be less readable for someone, is it bad I will use it as a measure instead of column? 

 

3) the code works, thanks for it

    (btw agree that DAX is not simple, but definitively not is simple)

 

Anonymous
Not applicable


@ph wrote:

Thanks Darek for response 🙂

 

1) thx for tip, I find only book called 'The Definitive Guide to DAX: Business Intelligence with Microsoft Excel, SQL ...' did you mean this? 🙂

 

--> Yes. I'd suggest to get the newest version of it. When you start reading this, it'll open your eyes.

 

2) Why I should  avoid to 'left(Account[Accounting Class]; 3)', I agree that code can be less readable for someone, is it bad I will use it as a measure instead of column? 

 

--> First, it's ugly. Second, you're doing at query time something that could have been done at process time. This hurts performance. Third, stunts in code are BAD if they can easily be avoided. Fourth, you're not using IT as a measure. You're trying to force the engine to perform an operation on a column. This - as I said - takes precious CPU cycles of the Formula Engine (and this engine is single-threaded!). Writing code is not just about writing code. It's about writing PERFORMANT AND WELL READABLE CODE.

 Best

Darek

* (btw agree that DAX is not easy, and definitively not is simple)

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (594)