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

Hi,

Is this the result you are expecting?

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    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!

  • yousaf's avatar
    yousaf
    Helper I
    5 years ago

    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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    Is this the result you expect to see for 2020 and Plant Z?

  • yousaf's avatar
    yousaf
    Helper I
    5 years ago

    Yes. This is correct.

  • yousaf's avatar
    yousaf
    Helper I
    5 years ago

    Currently testing it. I'll let you know soon. Thanks