Forum Discussion

Helper I

5 years ago

Solved

Need help

Hi Guys,, I am trying to replicate a web report in power bi. There is a graph which is shows the overall equipment rating based on tests done every month, rating being Legend here. Now the problem i...

Need Help

Jihwan_Kim
5 years ago
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 )
RETURN
withresult

Then, 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] )
)
RETURN
IF (
ISBLANK ( [count of equipment] ),
BLANK (),
COUNTROWS ( filtertable )
)

https://drive.google.com/file/d/17f_Q-xaoBpOodSLfT1WKq6g9y5Vyy585/view?usp=sharing

Jihwan_Kim

Super User

5 years ago

Hi,

Thanks for your feedback.

I am sorry that my visualization was not the one.

However, if your problem was related to the date-month concern, can you try to switch the format of your date? For instance, instead of writing dd-mm-yyyy, try mm-dd-yyyy. It depends on what format is your computer system using, but if you face a problem that does not correctly consolidate the month, sometimes it is because of the date-format-issue.

Thank you very much.

yousaf

Helper I

5 years ago

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.

Ashish_Mathur
Super User
5 years ago
Hi,

Share the link from where i can download your PBI file.
- yousaf
  Helper I
  5 years ago
  Link sent.
  - Ashish_Mathur
    Super User
    5 years ago
    Share the link here.