Forum Discussion

yousaf's avatar
yousaf
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's avatar
    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 )
    )
     
     
Jihwan_Kim's avatar
Jihwan_Kim
Super User
5 years ago

Hi,

Thank you very much for your explanation, and I also got your sample data by message.

Thank you.

Sorry to ask many questions to your question, but if you do not mind, could you please check whether you want to show the information like in the below picture in a stacked bar chart, or am I still missing something?
Terribly sorry that it doesn't look like meet your requirement, but if I wanted to show all the information in the stacked bar chart, I had no choice but to come up with like the below picture.

 

yousaf's avatar
yousaf
Helper I
5 years ago

Hi,

Unfortunately this is not what i need. What I'm after is one bar for all equipment, colored according to the rating, against each month. Similar to the picture3 in my original post.

I've tried many measures, but the date filter just isnt allowing for all equipment to show in one bar for a specific month.