Need help

Hi All, Needing some help with the measure below. I'm sure it needs to be written differently with a SWITCH function or something similar, but I'm stuck. Latest Status = CALCULATE( LASTNO...

Need Help

Anonymous

Not applicable

5 years ago

Sorry for the need to scrub data, just have to do it for privacy issues.

I need to be able to return the Compliance Status column that coresponds to the last visit date.

In this example, I have a visit on 7/24 and 8/13 for store 67. I am trying to get the "Display Built on Previous Visit" response to return into a table.

We currently have the measure for that column written as:

LatestStatus =
CALCULATE(
LASTNONBLANK('View Responses' [Compliance Status], 0),

FILTER('View Responses','View Responses'[Visit Date] = [Latest Visit Date User]))

Latest Visit Date User is simply:

LASTDATE('View Responses'[Visit Date])

I have tried MAXA for the visit date, I've tried using it as an IF statement ahead of the CALCULATE function, and several other options, but it is always returning the value for the visit on 7/24.

The desired output is below. Basically I will have Store#, The latest Visit Date, and The Latest Status returned from that measure.

Hope this helps. Thanks!

Anonymous

Not applicable

5 years ago

HI Anonymous,

Currently, you can't use filter/slicer(measure formulas are dynamic changes based on row contents) to create dynamic calculated columns/tables.

They are work on different data levels and calculated column/table not responsive or interaction with filter/slicer selections. (Calculate column/table are host on data model table, they are the parent level of the virtual tables that filter/slicer hosts; child-level not able to affect their parent)

Regards,

Xiaoxin Sheng

Forum Discussion

Need help