topic Re: Filtering table in DAX Commands and Tips

Filtering table

PZU_DGW — Wed, 11 Aug 2021 09:21:07 GMT

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"

)

Re: Filtering table

Anonymous — Wed, 11 Aug 2021 11:31:15 GMT

// 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.

Re: Filtering table

Jos_Woolley — Wed, 11 Aug 2021 11:38:53 GMT

Hi,

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

Regards

Re: Filtering table

PZU_DGW — Thu, 12 Aug 2021 14:47:49 GMT

Thanks Jos!

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

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

Greetings PZU

Re: Filtering table

Jos_Woolley — Thu, 12 Aug 2021 17:24:42 GMT

You're welcome!

Cheers