Home Forums Power Pivot Returning the Date when a value goes over the limit

This topic contains 5 replies, has 2 voices, and was last updated by  lilzenbca 3 months ago.

Viewing 6 posts - 1 through 6 (of 6 total)
  • Author
    Posts
  • #9630

    lilzenbca
    Participant
    • Started: 12
    • Replies: 13
    • Total: 25

    All,

    I currently have a dataset with the following columns:

    Date – Daily date

    Name – String descriptor

    Value – Numeric from 1 – 150

     

    What I need to do is set a measure that will return the date at which the value first crosses (IE greater than) 50. If I was in Excel, this is a simple Index/Match or VLookup. However, I’m running into issues with DAX and Power BI. I’d like to set up a matrix table in Power BI that returns the dates of initial¬†crossings for each Name.

    Thanks to all in advance.

     

    Brandon~

    #9631

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2558
    • Total: 2565

    Brandon,

    Much depends on whether you are looking for the absolute first time a value crosses 50 for a “name” or you will want to know for various filtered scenarios what is the first time that a value crosses 50 for a “name”. Please advise which case you are working with.

    Also, does your model only have one table? Or do you have (1) a calendar table and/or (2) a lookup table for the names.

    Tom

    #9633

    lilzenbca
    Participant
    • Started: 12
    • Replies: 13
    • Total: 25

    Tom,

    If I understand your question correctly, you are asking if there will be multiple scenarios where I will want to calculate the 50 cross for a “name”? Right now, the data is fairly linear and should only cross a single time. That is the general expectation going forward, so let’s just consider the first single cross.

    The model has a calendar table and a name lookup table.

    Thanks.

    Brandon~

    #9640

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2558
    • Total: 2565

    Since you are only interested in one event (the absolute first or only time), and you have a lookup table that has the names to be associated with this event, it makes more sense to me to have a calculated column in the names table than to create a measure.

    For some general ideas, you could consider the following:

    Calc Column in Names Table

    First Time Ever Name Crosses 50 =
    MINX (
        FILTER ( RELATEDTABLE ( Scores ), Scores[Results] > 50 ),
        Scores[TestDate]
    )

    Measure (assumes that you are using the Name column from the Names table on either pivot rows or pivot columns)

    First Time Ever Name Crosses 50 Measure =
    IF (
        HASONEVALUE ( Names[Name] ),
        VAR vName = Names[Name]
        RETURN
            MINX (
                FILTER ( ALL ( Scores ), Scores[Name] = vName && Scores[Results] > 50 ),
                Scores[TestDate]
            )
    )

    You might find

    #9641

    lilzenbca
    Participant
    • Started: 12
    • Replies: 13
    • Total: 25

    I ended up using the calculated column. That worked wonderfully.

     

    Thanks!

    #15984

    lilzenbca
    Participant
    • Started: 12
    • Replies: 13
    • Total: 25

    Let’s add some complexity to this. Say I have another table:
    Name – unique
    Threshold

    There’s a relationship by the Name field to both tables.

    How would I work this to use that threshold in the calculation instead of the static 50?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic.