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,

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