Can't get this measure to work - I see the problem, can't find the solution. Please HELP!

[Anonymous]

I have an issue which I've been working on for about a week, and several users here have helped me get closer to the solution, but I still can't get it right, but it doesn't seem like it should be so difficult, based on the relative simplicity of the data.

Here's the scenario: My data has multiple plants, and each plant has multiple turbines, and each of those turbines can have multiple alarms go off on a daily basis. So we have plant_code, alarm_code, and turbine_code. What I need to do is to count up all the alarms for each plant (for a given date range), and identify the top 5 of those alarms. Once I have those top 5 alarms, I need to then find out which turbines (by plant) had the most instances of those 5 alarms, so they know which were most impacted.

For more details on this data, here's my original thread, although I don't think it needs to be referenced now to solve this current issue: https://community.powerbi.com/t5/Desktop/Multiple-group-by-ranking-issue/td-p/593162

The .pbix I'm working with is stored here: https://drive.google.com/drive/folders/1al0BHPaSDibkgi5Axxgm_aIILKZLo0jE?usp=sharing

So this works when I filter for plant 1, and alarm code 3634. The most common alarm is 3634, it has 10 instances, with one turbine - F09, having the alarm twice. So ultimately the only row I want to see for this plant/alarm is that row, which represents the most impacted turbine. The row where "test" = 1 is what I want, and I can easily filter the visual for all rows where test = 1 once I add in more data.

So far, so good. And if I add another alarm to my slicer, in this case 3661, everything is still good. "Test" = 1 for this new row, as it's the only turbine associated with that alarm anyway.

But here's where I run into trouble: if I add alarm 3674 to my slicer, then my value for "Highest number of alarms at alarm_code level" goes from 2 to 3 for alarm 3634, even though no turbine for 3634 had more than 2 instances of that alarm. It seems pretty obvious to me that this number increased because alarm 3674 was tied to turbine G11, and G11 was already one of the turbines in my list for 3634. So I feel like this is a simple filtering issue, I just need to make "Highest number of alarms at alarm_code level" by filtered more specifically for specific alarms, as right now that "3" indicates it's adding it up for multiple alarms. Here's what the broken data looks like:

Does anyone know how I can make this work? It's fairly simple 3-level data with normal parent/child relationships, but I'm still pretty new to Power BI and haven't figured out how to manage these measures as I need to, particularly how to have them filter.

Any help is much appreciated!

thanks,

Alex

[v-shex-msft]

HI @Anonymous,

I'd like to suggest you to use modify your measure to use max function to get highest count:

Highest number of alarms at Alrm_code level =
MAXX (
    TOPN ( 1, ALL ( top_alarms[turbine_code] ), [Number of alarms] ),
    [Number of alarms]
)

In addition, I am not so recommend you to use sum function in measure to get current value. It will get wrong result when it calculated on total level. (you can use max, min to get current row contents)

Regards,
Xiaoxin Sheng

[Anonymous]

@v-shex-msft,

Thank you, that seems to work! However, I have since realized I have one other related issue, which is how to deal with ties. In this example, rank 2 is tied between alarm codes 77 and 141, so I need a tie-breaker to rank them 2 and 3. The tiebreaker comes from the alarm_duration field. Whichever of these alarms has the highest duration gets ranked harder. And then once that's figured out, if we have tied turbines at the alarm level then they also need to use duration to break the tie.

I created a new alarm_duration measure, modeled after the alarm_count one, and the data seems ok, but I can't figure out how to incorporate it into the ranking to break the ties. Is that anything you might be able to help with?

The PBI file is here: 

I'm learning so much here, thank you!

Alex