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
Gschie
New Member

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
greggyb
Resident Rockstar
Resident Rockstar

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
Resident Rockstar
Resident Rockstar

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.

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
Resident Rockstar
Resident Rockstar

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

Yesss! it worked, Thank you 🙂

 

/Gunnar

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.