Forum Discussion

yousaf's avatar
yousaf
Icon for Helper I rankHelper 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
Icon for Helper I rankHelper 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.

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper 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
    Icon for Helper I rankHelper I
    5 years ago

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