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,

Please correct me if I wrongly understood.

When I saw your desired outcome picture, I thought the gray-colored-area, that you also wanted to display, is a "not-tested-equipment-count".
If I am not wrong, in this case, I usually create one more data point for "others", for instance, and create a DAX measure that shows numbers for "others".
If it is OK with you to share your data with me, perhaps I can try to create a calculated measure to describe in a stacked column chart.

Thanks.

yousaf's avatar
yousaf
Helper I
5 years ago

I don't know how to share data here