Forum Discussion

Ncf5031's avatar
Ncf5031
Icon for Advocate I rankAdvocate I
3 years ago
Solved

Subtract Row Value From Table Row Count - KM Survival Curve

Needing help with two seemingly simple issues. 

 

First, the goal of this analysis is to peform a KM Survival Analysis. I have searched here and the articles pertaining to that analysis don't quite apply to my dataset (particularly referencing the article that Greg_Deckler wrote back in 2018.)

 

I'm attempting to emulate a table with the following format

 Currently I have the time at event and the count of failures at each time. What I'm having trouble with first is the total pieces of equipment operating at a current time. I am attempting to accomplish this by doing a COUNTROWS() of the table I am working with and subtracting the number of failures that occured at each hour count. My table looks like the following

 

EQUIP_HRSFAILURE_AT_TIME
Blank1
01
501
1001
2001

 

So as mentioned I would need the output to be:

EQUIP_HRSFAILURE_AT_TIMEALIVE_AT_TIME
Blank14
013
5012
10011
20010

 

Can anyone suggest a DAX expression that will accomplish this? I have gotten ~close with the following:

ALIVE_AT_TIME = COUNTROWS('FAILURES') - SUMX(FILTER(ALL('FAILURES'),' FAILURES'[EQUIP_HRS]<=EARLIER('FAILURES'[EQUIP_HRS])), FAILURES'[FAILURES_AT_TIME])
 
Beyond that I will need a running product calculation, but there's a ton of reference materail out there for that.
 
Thanks!
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Ncf5031 ,

     

    Please try this measure:

    ALIVE_AT_TIME = 
VAR __EQUIP_HRS = MAX('FAILURES'[EQUIP_HRS])
VAR _Count_1 = CALCULATE(COUNTA('FAILURES'[EQUIP_HRS]),'FAILURES'[EQUIP_HRS]>=__EQUIP_HRS)
VAR _Count_2 = COUNTBLANK('FAILURES'[EQUIP_HRS])
VAR _Count_3 = IF(ISBLANK(__EQUIP_HRS),_Count_1+_Count_2,_Count_1)
VAR _Result = _Count_3 - [FAILURE_AT_TIME]
RETURN
_Result

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Ncf5031 ,

     

    It seems that the data type of the field [EQUIP_HRS] is text and a new column needs to be created to assist in sorting.

    Please new a conditional column in PQ like:

    Sort by column:

    Then please new a measure:

     

    ALIVE_AT_TIME =
SUMX (
    FILTER (
        ALL ( 'FAILURES' ),
        'FAILURES'[EQUIP_HRS_Sort] >= MAX ( 'FAILURES'[EQUIP_HRS_Sort] )
    ),
    [FAILURE_AT_TIME]
) - [FAILURE_AT_TIME]

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

  • Ncf5031's avatar
    Ncf5031
    Icon for Advocate I rankAdvocate I
    3 years ago

    Anonymous the Equip_hrs column is not a text column, the first row is showing blank because that failure was entered into the database withthout an hour count and is therefore null.

    I tried using your solution but did not come up with the expected results. In my failures table there is a total of 800 rows at present. I would expect the first row to be 795, then 794, etc.

    The DAX I'm using for Failures_At_Time = 

    CALCULATE(DISTINCTCOUNT('FAILURES'[FAILURE_ID]), ALLEXCEPT('FAILURES','FAILURES'[EQUIP_HRS]))

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Ncf5031 ,

       

      Please try this measure:

      ALIVE_AT_TIME = 
VAR __EQUIP_HRS = MAX('FAILURES'[EQUIP_HRS])
VAR _Count_1 = CALCULATE(COUNTA('FAILURES'[EQUIP_HRS]),'FAILURES'[EQUIP_HRS]>=__EQUIP_HRS)
VAR _Count_2 = COUNTBLANK('FAILURES'[EQUIP_HRS])
VAR _Count_3 = IF(ISBLANK(__EQUIP_HRS),_Count_1+_Count_2,_Count_1)
VAR _Result = _Count_3 - [FAILURE_AT_TIME]
RETURN
_Result

      Best Regards,
      Gao
      Community Support Team

       

      If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

      How to get your questions answered quickly -- How to provide sample data

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Ncf5031 ,

       

      Any update?

       

      Best Regards,
      Gao
      Community Support Team

  • Ncf5031's avatar
    Ncf5031
    Icon for Advocate I rankAdvocate I
    3 years ago

    Anonymous my apologies for not updating. Your solution worked, I appreciate your assistance.