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
PZU_DGW
Regular Visitor

Filtering table

As a beginner in DAX I have this report that can be filtered on a specific year and some other filters. But I want to calculate a measure that is a snapshot, not depending on a specific year or other shown filters. I tried using 'ALL' and 'Calculate', but don't get the right answer. So I need some help 😉

 

I use a fact-table 'Paymentschemtable' (with a column PaymentID, SchemStatus and Contract-ID), which is filtered by another table 'Contracts' (with colums Contract ID and ContractStatus). I need to calculate the total PaymentID's, where SchemStatus=0 and ContractStatus <>"Ended". I made this measure, but the result is always the totalrows PAYMENTSCHEM where SCHEMSTATUS=0:

 

Measure =
CALCULATE(
COUNTROWS(
FILTER(
ALL(PAYMSCHEMETABLE),
PAYMSCHEMETABLE[SCHEMSTATUS]=0
)
),
CONTRACTS[ContractStatus]<>"Ended"
)
1 ACCEPTED SOLUTION

Thanks Jos!

 

That's the solution I was looking for! I forgot to add the '

ALL(PAYMSCHEMETABLE),'. It did the trick.
 
Greetings PZU

View solution in original post

4 REPLIES 4
Jos_Woolley
Solution Sage
Solution Sage

Hi,

MeasureALL =
CALCULATE (
    COUNTROWS ( PAYMSCHEMETABLE ),
    ALL ( PAYMSCHEMETABLE ),
    PAYMSCHEMETABLE[SCHEMSTATUS] = 0,
    CONTRACTS[ContractStatus] <> "Ended"
)

Regards

Thanks Jos!

 

That's the solution I was looking for! I forgot to add the '

ALL(PAYMSCHEMETABLE),'. It did the trick.
 
Greetings PZU

You're welcome!

 

Cheers

Anonymous
Not applicable

// First of all, please don't torture
// the end user with names of tables
// written the way they are. Make them
// pleasant to the eye. Respect your
// users and future developers.

// Second, you should never slice and
// dice in the UI by columns in a fact
// table. It's dangerous and you're risking
// that your measures will one day stop
// working correctly, of which you will not
// even be aware of.

[Measure] =
CALCULATE(
    COUNTROWS( PaymentScheme ),
    // You can remove KEEPFILTERS if the column
    // it wraps will not be used in the UI to
    // slice and dice by.
    KEEPFILTERS(
    	PaymentScheme[SchemeStatus] = 0
    ),
    // Same remark applies to this one. If
    // you have a field that you're planning
    // on using in the UI, please make sure
    // it's written decently, with spaces between
    // the constituent words. Respect your
    // audience.
    KEEPFILTERS(
    	CONTRACTS[ContractStatus] <> "Ended"
    )
)

// Please learn about KEEPFILTERS to know when
// to use it. You might need to remove this
// modifier here to obtain what you want. I can't
// from your description know whether or not
// it's required. It's your task to figure this
// one out.

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.

Top Solution Authors