Forum Discussion

M4dsteve's avatar
M4dsteve
Frequent Visitor
5 years ago
Solved

Calculated Column (Probability of Exceedance) Using Earlier Values

Hi all,   I am having some difficulty with a calculation that is used to calulate a probability of exceedance in a given data set.   Long story short this is for the Industrial Hygiene industry a...
Need Help
  • M4dsteve's avatar
    M4dsteve
    5 years ago

    Hi v-janeyg-msft ,

     

    I managed to solve this with a python script that outputs the data after processing the recursive calculation. 

     

    Turns out pandas is super powerful and it was a good learning experience 😄

    Snippet of code from that column:

     # Build pdep Column
    for i in range(0, (len(support))):
        if support.loc[i, 'IsNDValue']:
            na = 0
        else:
            na = support.loc[i, 'Exposure Result mg_x']
        nbj = support.loc[i, 'n_bj']
        if i != 0:
            prev = support.loc[i - 1, 'pdep']
            support.loc[i, 'pdep'] = prev + (1 - prev) * na / (na + nbj)
        else:
            prev = 0
            support.loc[i, 'pdep'] = prev + (1 - prev) * na / (na + nbj)

     

     

    Thanks for your help.

v-janeyg-msft's avatar
v-janeyg-msft
Community Support
5 years ago

Hi, M4dsteve 

 

I don’t understand your logic very well, but this seems to be a recursive problem. If it involves recursive calculations, Dax function is difficult to do due to the limitations of the dax language, but excel is simple. You can calculate it in excel first, and then bring data into powerbi, it may be the easiest way.

Best Regards

Janey Guo

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

M4dsteve's avatar
M4dsteve
Frequent Visitor
5 years ago

Thanks v-janeyg-msft,

 

Yes you are correct in that the 'i' reference is the problem here as it is calculating up the spreadsheet with the values in the row below.

 

I began with this table turned upside-down in Power BI and had an IF statement for if row index is 1 then previous value is 0 but then I cannot reference previous values in that column after that anyway.

 

I had a look through the Power Query M documentation but didn't see anything that would suit in this case. Unfortunately our data source is not very flexible so I am unable to calculate this prior to the import to BI.

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    5 years ago

    Hi, M4dsteve 

     

    Regarding the problem of recursion, some powerbi experts have studied it, and there is no good way. DAX and M are not easy to handle. What do you mean by inflexible data sources? If there is a lot of data, you can import it in batches. Have you tried to use the $A$ dynamic reference form to calculate the result in excel? 

     

    Best Regards

    Janey Guo

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • M4dsteve's avatar
      M4dsteve
      Frequent Visitor
      5 years ago

      Hi v-janeyg-msft ,

       

      I managed to solve this with a python script that outputs the data after processing the recursive calculation. 

       

      Turns out pandas is super powerful and it was a good learning experience 😄

      Snippet of code from that column:

       # Build pdep Column
    for i in range(0, (len(support))):
        if support.loc[i, 'IsNDValue']:
            na = 0
        else:
            na = support.loc[i, 'Exposure Result mg_x']
        nbj = support.loc[i, 'n_bj']
        if i != 0:
            prev = support.loc[i - 1, 'pdep']
            support.loc[i, 'pdep'] = prev + (1 - prev) * na / (na + nbj)
        else:
            prev = 0
            support.loc[i, 'pdep'] = prev + (1 - prev) * na / (na + nbj)

       

       

      Thanks for your help.

      • v-janeyg-msft's avatar
        v-janeyg-msft
        Community Support
        5 years ago

        Hi, M4dsteve 

         

        I am very happy that you can use python to solve the problem, because dax is a data analysis language and is not suitable for calculation iteration. It is your ability to use multiple languages fluently.😆

        You can mark your answer as solution.

         

        Best Regards

        Janey Guo