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, 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

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