2

I am working in Power BI to create a visualization about project reviews and statuses. I'm using Dax to try to accomplish two things.

I've done some research and it seems I should be using the last known value, but I keep getting a circular reference.

First, projects have checkpoint reviews before proceeding to the next evolution. This may happen once a year, or multiple times a year. Projects are also reporting status on a project, quarterly if they're performing great or good, and monthly if average or underperforming. The frequency will change if a project goes from good to average and vice versa.

For each review, I want to correlate whatever the last known reported status for a project is.

Correlating Project Review to Status and Using last known for new column

I used this code to bring in the project status data point I have into my review table

Overall Status =
LOOKUPVALUE (
    'Project Status'[Status],
    'Project Status'[Project Title], 'Review'[Project Title],
    'Project Status'[YearMonth], 'Review'[YearMonth],
    'Project Status'[Attribute], "Overall",
    ""
)

This only brings in those items that match so I then created a new column with the following

New Overall =
LOOKUPVALUE (
    Review[Project Overall Status],
    Review[Review Date],
        CALCULATE (
            LASTNONBLANKVALUE ( Review[Review Date], 1 ),
            FILTER (
                Review,
                Review[Project Overall Status] > 0
                    && EARLIER ( Review[Review Date] ) >= Review[Review Date]
            )
        )
)

This is where I get the circular reference. Ideally, I'd like to get the status from the project status table. I was trying to use this as a workaround. If I can resolve the circular that will work, but ideally the end state would just use the last known status from the project status table.

Ideal end state for correlation project status to reviews

The second thing I want to do is create a table that uses the last known project status like this:

Last Known Project Status

I have created a date table for 2020, but I am unsure how to properly use it. There are a lot of resources on how to create a date table, but I didn't find too many that showed how to use it in a meaningful way.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
ldc9819
  • 21
  • 2

1 Answers1

0

First, calculate the last Year Month for that project and then use that value in your lookup:

New Overall = 
VAR CurrProject = Review[Project]
VAR CurrYearMonth = Review[Year Month]
VAR LastYearMonth =
    CALCULATE (
        MAX ( 'Project Status'[Year Month] ),
        'Project Status'[Project Title] = CurrProject,
        'Project Status'[Year Month] <= CurrYearMonth
    )
RETURN
    LOOKUPVALUE (
        'Project Status'[Status],
        'Project Status'[Attribute], "Overall",
        'Project Status'[Year Month], LastYearMonth,
        'Project Status'[Project Title], CurrProject
    )
Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • This is also returning an error: "A circular dependency was detected: Review[New Overall], Review[Overall], Review[New Overall]." – ldc9819 Nov 10 '20 at 18:44
  • It definitely worked for me. I'd try deleting one or the other calculated columns so they aren't trying to reference each other. I.e. replace the formula for Overall with the DAX I provided instead of creating another column. – Alexis Olson Nov 10 '20 at 18:51
  • I went ahead and deleted both columns created a new one, now I get "A table of multiple values was supplied where a single value was expected." – ldc9819 Nov 10 '20 at 19:15
  • Hmm. That only makes sense to me if you tried to create a measure instead of a calculated column. – Alexis Olson Nov 10 '20 at 19:19
  • All the inputs are data points, beside the Year Month which was a lookup from the date table. The suggestion you made I definitely put into a calculated column. Are there are any other possibilities to fix this? – ldc9819 Nov 10 '20 at 19:31
  • Then I think you need to update your question to include all the tables and relationships involved. It's really difficult to answer questions when there's stuff going on that hasn't been specified. – Alexis Olson Nov 10 '20 at 20:44
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/224379/discussion-between-ldc9819-and-alexis-olson). – ldc9819 Nov 10 '20 at 20:48