Forum Discussion
Need help
Hi,
I hope the below is what you are looking for.
I created a measure that gives The LATEST Rating result of the equipment among the current or from before until the current month. I also considered if one Equipment has two Ratings on the same day, then give the maximum result of Ratings, because "selectedvalue(Data[Rating])" gives blank value if it has two results.
test result (if it is not in this month, show last month result) =VAR lasttestdate =CALCULATE (MAX ( Data[Test_Date] ),FILTER ( ALL ( dates ), dates[Date] <= MAX ( dates[Date] ) ))VAR withresult =CALCULATE ( SELECTEDVALUE ( Data[Rating], MAX(Data[Rating]) ), dates[Date] = lasttestdate )RETURNwithresultThen, I created the below measure.equipment count by test result =VAR newtable =SUMMARIZE (ALL(Equipments),Equipments[Equipment],"@testresult",[test result (if it is not in this month, show last month result)])VAR filtertable =FILTER (newtable,[@testresult]= SELECTEDVALUE ( Ratings[Rating] ))RETURNIF (ISBLANK ( [count of equipment] ),BLANK (),COUNTROWS ( filtertable ))
I have 100 equipment. Every equipment has different test frequency. In a given month, not every equipment is tested but I still have last known rating of the equipment for which there is no test in that specific month. Now what I want to show (as in Picture 3 in my original post) is that for each month, what was the latest ratings for all 100 equipment. This way, the total number will remain 100 every month in the grapgh, but the rating distribution (color based on legend) will change based on last available rating.
for example, in january, 35 equipment were tested. So i have latest ratings on these 35 equipment. But as the rest of the equipment also have previous rating, the graph needs to show all 100, with last available rating.
I hope it is more clear now.
Hi,
Share the link from where i can download your PBI file.
Hi,
I hope the below is what you are looking for.
I created a measure that gives The LATEST Rating result of the equipment among the current or from before until the current month. I also considered if one Equipment has two Ratings on the same day, then give the maximum result of Ratings, because "selectedvalue(Data[Rating])" gives blank value if it has two results.
test result (if it is not in this month, show last month result) =VAR lasttestdate =CALCULATE (MAX ( Data[Test_Date] ),FILTER ( ALL ( dates ), dates[Date] <= MAX ( dates[Date] ) ))VAR withresult =CALCULATE ( SELECTEDVALUE ( Data[Rating], MAX(Data[Rating]) ), dates[Date] = lasttestdate )RETURNwithresultThen, I created the below measure.equipment count by test result =VAR newtable =SUMMARIZE (ALL(Equipments),Equipments[Equipment],"@testresult",[test result (if it is not in this month, show last month result)])VAR filtertable =FILTER (newtable,[@testresult]= SELECTEDVALUE ( Ratings[Rating] ))RETURNIF (ISBLANK ( [count of equipment] ),BLANK (),COUNTROWS ( filtertable ))Can you please share your date table dax here? I'm unable to open your file as its newer version.
Hi,
Thank you for your message.
I thought you could open even the version is different. 🤔
I am adding links down below for the two files.
One is PBIX file (please try again), and the other is the date table csv file.
I usually create a date table by using M in Power Query Editor.
Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.date table link
https://drive.google.com/file/d/18vThqVa49HhUvmBZohSc3xkDrxsZDLNB/view?usp=sharing
pbix file link
https://drive.google.com/file/d/17f_Q-xaoBpOodSLfT1WKq6g9y5Vyy585/view?usp=sharing
Its still not really working for my data but anyways I'm really thankful and appreciate the help.🙂
Hi, yousaf
Please kindly have a look at the below picture.
I am not sure what happened in your pbix file, but I fixed the Equipments-Table and created a new one to match with the new data.
please also check the link below.
And, please kindly let me know if it works for you, otherwise, I will try to find a different way.
https://www.dropbox.com/s/xmpl5mpoho68fzo/yousaf%202021%200320.pbix?dl=0
If this helps, Appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!Link sent.
Share the link here.
Oh,
Perhaps,
try to assign the new-dates-table as a date table.
In some cases, if you do not assign the created date table as a date table, date-related-measures would not work.
I hope it helps.
Hi,
Is this the result you are expecting?
Yes. Exactly.
Jihwan's solution also worked on the sample data but isnt working on the main data. Plus, equipment will be filtered based on system, so keeping equipment constant also doesnt help.
Hi,
Share your main data in an MS Excel file. I only need the first 3 columns in that file (not the bins column. I have solved it without the bins column). Let me plug in that data to see whether my solution get your desired result or not.
Here is the link,
Hi,
Is this the result you expect to see for 2020 and Plant Z?
Yes. This is correct.
Currently testing it. I'll let you know soon. Thanks
Hi,
You may refer to my solution here - Determine latest condition of each equipment and show a month wise count.
Hope this helps.