Need Help Creating a Measure

[Anonymous]

All,

I have made a table in PBI and created a calculated column called ALL ERT /  PASS FAIL that is looking at two time fields. If the update time is greater than the Previous ERT at anytime, then it failed. Since there are multiple updates to each event, I need to create a measure that says anytime any update time fails, then the entire event is expired. 

I want to create a measure capturing each distinct event ID as either expired or not expired and throw that into a pie chart.

Below is the example table. I have highlighted the event id that is expired. I need it to only count as one and it to be expired. Any input would be greatly appreciated!

[Anonymous · Accepted solution]

@PaulDBrown had a posted a good solution while I was working on one.  Since there is always more than one way to solve a problem, I'll post mine as well.  Given this sample table:

I created two measures on the same table

Expired
    = CALCULATE (
        DISTINCTCOUNT ( Events[EventID] ),
        ALLEXCEPT ( Events, Events[EventID] ),
        'Events'[Alert] = "Expired"
    )

NotExpired
    = CALCULATE (
        DISTINCTCOUNT ( Events[EventID] ),
        ALLEXCEPT ( Events, Events[EventID] ),
        EXCEPT ( Events, FILTER ( Events, [Expired] > 0 ) ),
        'Events'[Alert] = "Not Expired"
    )

Place both measures on the visual.

If I'm understanding your problem and desired solution correctly, this should work.  The ALLEXCEPT keeps the filter within the scope of an EventID, and the NotExpired measure ignores Events that have and Expired Measure >0

[Anonymous · Accepted solution]

Sorry @Anonymous I made changes to my sample table after I screen shot it.  i updated my image. you'll see now that it does work, i just goofed on my post

[Anonymous]

Lastly - if you want to do it as a column in your table you could try this:
Create a calculated column with the following:

All Pass Fail =

VAR _eventid = PassFail[Event ID]

VAR _events = FILTER(ALL(PassFail), PassFail[Event ID] = _eventid && ISBLANK(PassFail[Previous ERT]) = FALSE() && PassFail[Update Time] > PassFail[Previous ERT])

RETURN IF(COUNTROWS(_events) > 0, "Expired", "Not Expired")

It will label all rows for an event id as "Expired" if any of the rows for an event id fails.
Now that it's a column you can place PassFail as the legend/axis and distinct count of Event ID as the values and it'll give you what you're looking for.  The benefit is that you could also use the PassFail column in a slicer if you had that reporting need.

Doesn't look like I can attach the PBIX.  Holler if you'd like a copy.

[PaulDBrown]

If you want to create a table (or any other visual) with a measure, here is one way:
The Measure to use as a filter for the (table) visual in the filter pane (set the value to 1)

Expired =
VAR Expired =
    CALCULATETABLE (
        VALUES ( 'Table'[Event ID] ),
        'Table'[ALL ERT Pass/Fail] = "Expired"
    )
VAR Vals =
    VALUES ( 'Table'[Event ID] )
VAR _Table =
    ADDCOLUMNS ( EXCEPT ( Vals, Expired ), "Status", "Not Expired" )
VAR _Exp =
    ADDCOLUMNS ( Expired, "Status", "Expired" )
RETURN
    COUNTROWS (
        INTERSECT (
            SUMMARIZE ( 'Table', 'Table'[Event ID], 'Table'[ALL ERT Pass/Fail] ),
            UNION ( _Table, _Exp )
        )
    )

To get:

 I've attached the sample PBIX file

[Anonymous]

Paul, this seems to give me the solution I want. Thank you much! Are you suggesting I make this measure in the table with the data, or create a measure as it's own table? 

[PaulDBrown]

It doesn't matter in which table you create the measure. Normally I keep my measures in  seperate tables, but it's very much a personal choice. Whatever you decide, it won't affect the result.
(please beware I altered the measure slightly to cater for possible multiple instances of any particular Event ID - You'll see I used SUMMARIZE instead of the original SELECTCOLUMNS in the return expression)

PS: Since the measure is counting individual IDs  it is essentially a distinct count. You can therefore also use the measure as the equivalent of a DISTINCOUNT of IDs and segment by the ALL ERT Pass/fail column.

[Anonymous]

Does this syntax look correct to you? I am getting an error now =/

[PaulDBrown]

The table in SUMMARIZE must be the fact table -the ERT_CHANGE_LOG table (not a measure table) - where the rows are, and SUMMARIZE only accepts columns from that table or related tables and aggregations (in other words you don't need the "Names" for each column since you aren't adding aggregations - see my example)

[Anonymous]

So I was able to replicate the table and it was perfect, but the pie chart with the exact same parameter seems to count event ids more than once and I cannot understand why for the life of me. I appreciate all your help with this. Feel like this is harder than it should be lol

[PaulDBrown · Accepted solution]

I'm not sure what is going on at your end. If there are several rows per Event ID, it still appears to return the correct result in a pie chart:

[Anonymous]

Dude, you reminded me to add the filter in there after screenshotting a picture of the filter. Thank you so much!!!! Really appreciate your help with this.

[Anonymous]

You could create another column with a numeric status code then your measure could look for the Max(StatusCode) for each event.  If a single event can only be expired or non expired you could also use a 1 or a 0 and then sum those up for each event.  Any expired events would equal 1.  If I'm over simplifying the problem let me know.

[Anonymous]

Thats a very good point. I may try that. The measure should auto group them together, right?

[Anonymous]

I think the idea is right, just do not know how to make that measure @Anonymous 

[Anonymous]

Just to clarify, do you only want to count only the most recent instance of each eventid and it's status, or do you want a summary of expired/NotExpired for each event?

[Anonymous]

So, essentially I want a distinct count of each event, but if anytime there was an update that "Expired" I want it to be expired regardless if every other update was "Not Expired". So like each event be counting as one regardless of how many updates occur on it. I hope this is clear enough. I can try and explain it more if it is still confusing 

[Anonymous · Accepted solution]

@PaulDBrown had a posted a good solution while I was working on one.  Since there is always more than one way to solve a problem, I'll post mine as well.  Given this sample table:

I created two measures on the same table

Expired
    = CALCULATE (
        DISTINCTCOUNT ( Events[EventID] ),
        ALLEXCEPT ( Events, Events[EventID] ),
        'Events'[Alert] = "Expired"
    )

NotExpired
    = CALCULATE (
        DISTINCTCOUNT ( Events[EventID] ),
        ALLEXCEPT ( Events, Events[EventID] ),
        EXCEPT ( Events, FILTER ( Events, [Expired] > 0 ) ),
        'Events'[Alert] = "Not Expired"
    )

Place both measures on the visual.

If I'm understanding your problem and desired solution correctly, this should work.  The ALLEXCEPT keeps the filter within the scope of an EventID, and the NotExpired measure ignores Events that have and Expired Measure >0

[Anonymous]

@Anonymous thanks for marking this as an alternative solution. @PaulDBrown table based solution is very good and I'm glad it works for you. I appreciate you acknowledging this Measure based solution as well

[Anonymous]

So I was able to get something similar to this, but as you can see, you only have 5 distinct event IDs but it is counting 8 on the pie chart. I am trying to stop it from count events with both expired and not expired tags separately and only count each event distinctly if that makes sense... 

[Anonymous]

I have the pie chart next to a table of a count of distinct event ids. I need that pie chart to match the distinct count of the event id

[Anonymous · Accepted solution]

Sorry @Anonymous I made changes to my sample table after I screen shot it.  i updated my image. you'll see now that it does work, i just goofed on my post

[Anonymous]

I appreciate all the help you have provide sir! Think we are on the right track