cancel
Showing results for 
Search instead for 
Did you mean: 
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
March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!