How to use a filtered card result in another card in order to perform a calculation

[Andy_W_UK]

Hello everyone,

I am hoping that the following is possible, if it is then please can someone advise how best to achieve this.

I have a card called Total Tasks Open 14+ Days showing the value 43 based on some filters.

I would like to take the result 43 and then in a new card, perform a calculation with that result 43 divided by another measure that I have created called 'Total Active Tasks' (which returns 67) in order to show the result of tasks opem 14+ days as a percentage.

I have attempted to do this is DAX Query View using the card code from Performance analyzer but as a beginner this is a bit beyond me so getting various errors !

I attach the card query below.

Many thanks !

// DAX Query

DEFINE

              VAR __DS0FilterTable =

                             FILTER(

                                           KEEPFILTERS(VALUES('SDT Clarity Tasks'[DaysOpen])),

                                           'SDT Clarity Tasks'[DaysOpen] >= 14

                             )

              VAR __DS0FilterTable2 =

                             FILTER(

                                           KEEPFILTERS(VALUES('SDT Clarity Tasks'[Assignee])),

                                           NOT(

                                                          'SDT Clarity Tasks'[Assignee] IN {"a.a@tba.com",

                                                                        "a.b@tba.com",

                                                                        "c.d@tba.com",

                                                                        "e.f@tba.com",

                                                                        "g.h@tba.com",

                                                                        "i.j@tba.com"}

                                           )

                             )

              VAR __DS0FilterTable3 =

                             FILTER(

                                           KEEPFILTERS(VALUES('SDT Clarity Tasks'[Customer])),

                                           NOT('SDT Clarity Tasks'[Customer] IN {"SC"})

                             )

              VAR __DS0FilterTable4 =

                             FILTER(

                                           KEEPFILTERS(VALUES('SDT Clarity Tasks'[Phase])),

                                           'SDT Clarity Tasks'[Phase] <> "Delivered"

                             )

              VAR __DS0FilterTable5 =

                             FILTER(

                                           KEEPFILTERS(VALUES('SDT Clarity Tasks'[Status])),

                                           NOT(SEARCH("Closed", 'SDT Clarity Tasks'[Status], 1, 0) >= 1)

                             )

EVALUATE

              SUMMARIZECOLUMNS(

                             __DS0FilterTable,

                             __DS0FilterTable2,

                             __DS0FilterTable3,

                             __DS0FilterTable4,

                             __DS0FilterTable5,

                             "CountID", IGNORE(CALCULATE(COUNTA('SDT Clarity Tasks'[ID])))

              )

[mark_endicott]

@Andy_W_UK - Filter context is not applied at the visual level, it is applied at the model level.

This means that whatever filters are being applied to your card to produce 43, need to be either applied to the whole page or within the DAX code that creates the [TOTAL TASKS OPEN 14+ DAYS] measure - this may even be a mixture of both, then the below will work:

 
DIVIDE( [TOTAL TASKS OPEN 14+ DAYS], [TOTAL ACTIVE TASKS], 0 ) 

I would start with:

CALCULATE( SUM( open tasks ), KEEPFILTERS( 'SDT Clarity Tasks'[DaysOpen] >= 14 ))

You can then add in any other filters that are not set at the page level. This means you do not need to add any filters that are also effecting the [TOTAL ACTIVE TASKS] measure.

If this works, please accept as the solution. 

[Andy_W_UK]

Thanks for the pointers Mark, I'm beginning to follow

I do not have the 'Total Tasks Open 14+ days' card as a measure, just the value 43 in the card and I wasn't sure if that can/should be created (?)

Also sorry if I'm misunderstanding but doesn't the Calculate statement just do the same as the card output ?

Thanks again

Andy 

[mark_endicott · Accepted solution]

@Andy_W_UK - CALCULATE( ) changes the context of a calculation. It does this by changing the filter context that is being applied to it. 

In my example the calculation is SUM ( open tasks ). And I am applying a new filter context to that calculation with:

KEEPFILTERS( 'SDT Clarity Tasks'[DaysOpen] >= 14 ))

This filters your 'SDT Clarity Tasks' table to only rows where DaysOpen is greater than 14 

You can create the measure for 'Total Tasks Open 14+ days' using the code I have given, and just change the SUM ( open tasks ) for:

COUNTA( 'SDT Clarity Tasks'[ID] )

This will just be your starting point, as you may have other filters that need to be added. So, an example of your full measure, with two of the filters you supplied in your PA code is below:

CALCULATE( COUNTA('SDT Clarity Tasks'[ID]) , 
       KEEPFILTERS( 'SDT Clarity Tasks'[DaysOpen] >= 14 ),
       KEEPFILTERS( 'SDT Clarity Tasks'[Phase] <> "Delivered") 
)

However, again, you should only include filters here that do not also affect the Total Active Tasks calculation - these filters can be applied at the page or visual level. 

If this helps, or works, please accept as the solution. 

[Andy_W_UK]

Thanks for explaining Mark, I have it all working now !