Drillthrough feature with comparing text data by date

[Anonymous]

Hi community,

I have a dataset that contains project management information with a list date, index, and project characteristics such as status, name etc. However, I want to count the number of projects where there has been a characteristic change from the most recent date. For example, in my picture below, a comparison for 3/10 and 2/2 shows that 2 of the projects have changed, for 3/10 and 2/16 2 projects have also changed. 

I have successfully gotten the two dates and the count of the amount of projects that have seen a change. Here are my DAX formulas:

Status Date1 = var a = CALCULATE(MIN(Project[Status]),FILTER('Date','Date'[List Date] = MIN('Date'[List Date])),VALUES('Change Calculations'[CAP #])

)Return

IF(ISBLANK(a),0,a)

Status Date2 = var a = CALCULATE(MAX(Project[Status]),FILTER('Date','Date'[List Date] = MAX('Date'[List Date])),VALUES('Change Calculations'[CAP #])

)Return

IF(ISBLANK(a),0,a)

Status Change = if([Status Date1] <> [Status Date2],TRUE,FALSE)

Count of Status Change =

VAR a =

SUMMARIZE ( 'Date', 'Change Calculations'[CAP #], "a1", [Status Change] )

RETURN

COUNTROWS ( FILTER ( a, [a1] = TRUE () ) )

However, the most important element of this is that the user gets to drillthrough to another page to see the full information of the projects that have changed characteristics. But when I use the "Count of Status Change" on a Gauge viz, it does not correctly filter my drillthrough page to the projects with a status change. 

At this point I'm wondering if there is a calculated column approach or a way to transform my "Count of Status Change" formula so it will correctly slice the data to the projects where there are been a characteristic change through a drillthrough. 

Here is the link to the sample data

Thanks in advance!

[v-jayw-msft]

Hi @Anonymous ,

Can't reach the sample data.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Should be fixed now, @v-jayw-msft  if not let me know and I'll share with you directly.