Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Ahead4097
Helper I
Helper I

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(
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.Table.PNG
 

Thanks!

 

4 REPLIES 4
MFelix
Super User
Super User

Hi @Ahead4097,

 

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Sample Data.PNG

 

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.

 Ouput Example.PNG

 

Hope this helps. Thanks!

HI @Ahead4097,

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @Ahead4097 ,

 

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.