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

How to filter data based om another filed in the database

Hello

 

I am new in this DAX language, so please help me.

 

I have at table it contains Amount and accountnumbers.

 

AcAm = Amount

AcNo = AcountNr

 

I want to sum(AcAM where AcNo >3000 and AcAm < 4000)

 

What is the syntax??

 

Gunnar Schie 

1 ACCEPTED SOLUTION

Accepted Solutions
greggyb New Contributor
New Contributor

Re: How to filter data based om another filed in the database

First, does this need to be hard-coded into a measure, or can you get away with using filters in your report?

 

If this needs to be in a measure, it's a simple call to CALCULATE():

 

Amount =
SUM( 'MyTable'[AcAm] )

Filtered Amount =
CALCULATE(
    [Amount]
    ,'MyTable'[AcNo] > 3000
    ,'MyTable'[AcAm] < 4000
)

CALCULATE() allows you to define simple predicates to filter expressions, or complex table expressions to define the context for the expression. All arguments 2-N of CALCULATE() are evaluated in a logical and.

View solution in original post

4 REPLIES 4
greggyb New Contributor
New Contributor

Re: How to filter data based om another filed in the database

First, does this need to be hard-coded into a measure, or can you get away with using filters in your report?

 

If this needs to be in a measure, it's a simple call to CALCULATE():

 

Amount =
SUM( 'MyTable'[AcAm] )

Filtered Amount =
CALCULATE(
    [Amount]
    ,'MyTable'[AcNo] > 3000
    ,'MyTable'[AcAm] < 4000
)

CALCULATE() allows you to define simple predicates to filter expressions, or complex table expressions to define the context for the expression. All arguments 2-N of CALCULATE() are evaluated in a logical and.

View solution in original post

Gschie Frequent Visitor
Frequent Visitor

Re: How to filter data based om another filed in the database

Thank you for quick response!

 

I'm not shure aboat the hard-code 😉

 

But this measure, i write this in table view. I connect my table in SQL database (Tabel AcTr)

I make a new messure: Amount

I make a new colomn: Filtered Amount.

 

Is this correct?

 

Gunnar Schie

greggyb New Contributor
New Contributor

Re: How to filter data based om another filed in the database

Both of those that I gave you should be defined as measures. Measures can be used in any visualization.

Gschie Frequent Visitor
Frequent Visitor

Re: How to filter data based om another filed in the database

Yesss! it worked, Thank you 🙂

 

/Gunnar

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)