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 )
    )
     
     
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.