Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'll try to be specific - but I think if I could word this right, Google would already have helped me! I've 100% built everything I needed from lurking here until now with searches.
I need a column (or 2) or measure (anything really) to determine if someone's qualification status has changed between yesterday and today. I'm a novice so my usual path is to do a conditional check for whatever, put a 1 in another column, and use all those 1s.
For my question:
So I need to know for each person (User) a qual status change. I need to account for the Curriculum ID as well, as in... an employee may have more than one qualification. My attempts keep getting tripped up by that.
This data is a few hundred thousand rows by now, every day everything just gets pulled in. But I'm just trying to limit to comparing yesterday with today.
I was thinking 2 columns/measures name GainedQual and LostQual that had a yes or 1 - basically for each row of data (user/qual pair)...
If Yesterday Curriculum Complete was YES and Today it's NO = LostQual
If Yesterday Curriculum Complete was NO and Today it's YES = GainedQual
If yesterday and today match, both are YES or NO, nothing changed and I don't care about it ;).
The point of all this is to get a tile (count) and list of names of anyone that changed - the tile to subscribe to if it goes over 0
Thank you kindly - happy to clarify, I know I'm describing rather poorly but I'm at the edge of my knowledge.
Solved! Go to Solution.
I was actually able to accomplish what I was after last night. I'm pretty new, and I really don't know what I'm doing so I just keep trying stuff until it works. This worked.
Yesterday Qual = LOOKUPVALUE('ERO Simple Qual (Over Time)'[Curriculum Complete],'ERO Simple Qual (Over Time)'[User & Curricula],'ERO Simple Qual (Over Time)'[User & Curricula],'ERO Simple Qual (Over Time)'[Date (Data)],'ERO Simple Qual (Over Time)'[Date (Data)]-1)
But thank you for your reply. I will look into that incremental refresh, I had not heard of that yet.
Hi @RVoodoo ,
According to my understanding, you want to get the value of "Curriculum Complete" from yesterday,but fail to use LOOKUPVALUE() ,right?
I think it is because [User & Curricula] column is used in LOOKUPVALUE function. Since its value is unique, value from yesterday must be different from and value on current day. In this case ,you could not find the matched value.
So please try to remove User & Curricula condition like this:
Remove User & Curricula condition = LOOKUPVALUE('ERO Simple Qual(Over Time)'[Curriculum Complete], [Date (Data)], [Date (Data)]-1)
Or in case your date(data) is Discontinuous, please try this:
If date is discontinuous =
var _last=MAXX(FILTER('ERO Simple Qual(Over Time)',[Date (Data)]<EARLIER('ERO Simple Qual(Over Time)'[Date (Data)])),[Date (Data)])
return LOOKUPVALUE([Curriculum Complete],[Date (Data)],_last)
In addition, as you said, This data is a few hundred thousand rows by now, every day everything just gets pulled in. But I'm just trying to limit to comparing yesterday with today.
It is suggested to use Incremental Refresh so that you could reduce the amount of data that has to be refreshed.
For more information,you can refer to these articles:
Incremental refresh for datasets in Power BI - Power BI | Microsoft Docs
Configure incremental refresh for Power BI datasets - Power BI | Microsoft Docs
All You Need to Know About the Incremental Refresh in Power BI: Load Changes Only
If it does not make sense, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.😀
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I was actually able to accomplish what I was after last night. I'm pretty new, and I really don't know what I'm doing so I just keep trying stuff until it works. This worked.
Yesterday Qual = LOOKUPVALUE('ERO Simple Qual (Over Time)'[Curriculum Complete],'ERO Simple Qual (Over Time)'[User & Curricula],'ERO Simple Qual (Over Time)'[User & Curricula],'ERO Simple Qual (Over Time)'[Date (Data)],'ERO Simple Qual (Over Time)'[Date (Data)]-1)
But thank you for your reply. I will look into that incremental refresh, I had not heard of that yet.
Hi @RVoodoo ,
Could you tell me if my post helps you a little? If it is, please kindly Accept it as the solution as well. Thanks in advance.😀
Best Regards,
Eyelyn Qin
@RVoodoo Thanks for providing detailed explanation. Good job!
It will be easier if you share a sample excel or pbix file (remove sensitive information before sharing) and provide the expected output. It will help to provide the solution.
You can share using google drive/one drive.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Well, it's all sensitive data. I'll build something soon perhaps. But as I keep poking and failing, I think I can accomplish what I need with one simple thing. I've combined the USER ID and Curricula columns. So now they are all unique (per day).
EDIT cuz I forgot to include the new column
So really now, I was hoping to add a new column (YesterdayQual) that just shows me the value of Curriculum Complete from yesterday for each row... I guess using the new column since it's the unique piece.
Here's my stab to maybe show the train of thought... I'm not sure if LOOKUPVALUE is the right thing for looking up within the same table - and I don't quite know how to look for 'yesterday'
User | Count |
---|---|
131 | |
108 | |
101 | |
67 | |
64 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |