cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors