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

Hi,

Thanks for the reply.

That grey area is also a rating.

Lets say we have 10 equipment. All 10 were tested in December. Then 5 were tested in January, 9 were tested in February. In March, 7 were tested and got rated x, y or z. Now what i need to show is the count of all 10 equipment and there rating (as legend) for January, February and March & so on. At the moment, I'm just getting count of equipment tested in a specific month, which does not give me an overall equipment rating picture.

 

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi,

    Thank you very much for your explanation, and I also got your sample data by message.

    Thank you.

    Sorry to ask many questions to your question, but if you do not mind, could you please check whether you want to show the information like in the below picture in a stacked bar chart, or am I still missing something?
    Terribly sorry that it doesn't look like meet your requirement, but if I wanted to show all the information in the stacked bar chart, I had no choice but to come up with like the below picture.

     

    • yousaf's avatar
      yousaf
      Helper I
      5 years ago

      Hi,

      Unfortunately this is not what i need. What I'm after is one bar for all equipment, colored according to the rating, against each month. Similar to the picture3 in my original post.

      I've tried many measures, but the date filter just isnt allowing for all equipment to show in one bar for a specific month.

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        5 years ago

        Hi,

        Thanks for your feedback.

        I am sorry that my visualization was not the one.

        However, if your problem was related to the date-month concern, can you try to switch the format of your date? For instance, instead of writing dd-mm-yyyy, try mm-dd-yyyy. It depends on what format is your computer system using, but if you face a problem that does not correctly consolidate the month,  sometimes it is because of the date-format-issue.

        Thank you very much.