Need help

[Anonymous]

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(

LASTNONBLANK('View Responses'[Option Text],0),

FILTER('View Responses','View Responses'[Question] = "What is the status of promotional compliance?" || 'View Responses'[Question] = "What is the status of promotional compliance"),

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

The measure for Latest Visit Date User is:

LASTDATE('View Responses'[Visit Date])

I have this measure that looks at a text column (Option Text) to return the last response in that column. The problem with using LASTNONBLANK is the data comes back to us with the dates mixed up. We tried sorting ascending on the date column, but it's still creating issues. 

Any ideas on how I can get the most recent response, and have the measure actually listen to the Latest Date filter?

Photo of data table included below. Please let me know if any other info would be helpful.

Thanks!

[MFelix]

Hi @Anonymous,

Can you please share some sample data and expected output for that sample data?

It's difficult to understand what is the value you want to have returned and the summarization based on the data provided.

Please see this post regarding How to Get Your Question Answered Quickly (courtesy of @Greg_Deckler) and How to provide sample data in the Power BI Forum (courtesy of @ImkeF).

Thank you

[Anonymous]

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!

[v-shex-msft]

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

[MFelix]

Hi @Anonymous ,

Sorry for the additional questions do you have any cases where in the same day you have more than one visit with text? or the maximum visits per day is always 2 where one is blank?

If you have two visits on the same day what is the tie breaker how do you know that was the last status that you want to display?

Because looking at this simplified information if you place the visit date and the compliance status and filter out all that is blank you will get the expected result, the only thing that you would probably need is to make a filter measure if you only want to get the last visit.

I may be missing the point of your information.