Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Dynamic calculate based on the parameter value provided by user

Hello,

Need some help with 2 issue .. 

 

1- I am trying to generate a buffer flag where the "EndignonHand" (EOH) is greater then 0 for the dates < today + # of weeks (provided by the user).. 

So for example if the user input for # of weeks as 2, then isinbuffer (calculated column)  (or isinbuffer-Check measure) should return "N" where EOH is <0 and the weekdate <= (6/11/2023 + 14days) and the visuals (like table or line and  stack chart) should change dynamicly.

 

2- I have a RawDataTable based on which I need to derive a summerzied table which should have the first row (minimum date) of each itemkey whose "EndignonHand" <=0 however if the itemkey does not have a row where EOH<=0 then take row that has the minimum date for that item.. basically build the SummerizedTable dynamically in powerbi.. 

below are the measures that i have tried.. it works but is not dynamic..

 

 

IsInBuffer-check = calculate(if(min(SummarizedTable[EndingOnHand])<=0 && min(SummarizedTable[Weekdate].[Date])<= [Today+Weeknum], "N","Y")) <calculated column>

IsInBuffer = if(SummarizedTable[EndingOnHand]<=0 && SummarizedTable[Weekdate]<= [Today+Weeknum], "N","Y")

Today+Weeknum = VAR selectedweeknum = SELECTEDVALUE ( '# of Week Param'[# of Week Param]) RETURN TODAY()+selectedweeknum*7

Total Items = CALCULATE(DISTINCTCOUNT(SummarizedTable[Item]))

Total Items(Meet Buffer) = CALCULATE(DISTINCTCOUNT(SummarizedTable[Item]),filter(SummarizedTable,SummarizedTable[IsInBuffer]="Y"))

Total Items(not Meet Buffer) = CALCULATE(DISTINCTCOUNT(SummarizedTable[Item]),filter(SummarizedTable,SummarizedTable[IsInBuffer]="N"))

Buffer Coverage = CALCULATE(DIVIDE([Total Items(Meet Buffer)],[Total Items],0))

 

Here is the sample powerbi with all the above datasets and code.

Sample for dynamic calculation.pbix 

 

 

Let me know if anyother details are required..

Thanks!

 

 

Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , Anonymous 

    Thanks for your quick response!I download your pbix file and i can open it successfully!

    For your two needs , here are the steps you can refer to :
    (1)We can just modify your [IsInBuffer-check] measure like this:

    IsInBuffer-check = if(min(SummarizedTable[EndingOnHand])<=0 && min(SummarizedTable[Weekdate])<= [Today+Weeknum], "N","Y")

    Then we can get this:

    (2)For your second need , we can click "New Table" and enter this:

    Table 3 = ADDCOLUMNS( ADDCOLUMNS (
    SUMMARIZE (
        RawDataTable,RawDataTable[Filekey],RawDataTable[Item],RawDataTable[Itemkey],RawDataTable[location],RawDataTable[Productname],RawDataTable[Source]
    ),
    "Weekdate",  var _t =  CALCULATETABLE('RawDataTable')  var _t2 = FILTER(_t,[EndingOnHand]<=0)   return IF(MINX(_t2,[Weekdate])=BLANK() , MINX(_t,[Weekdate]) ,MINX(_t2,[Weekdate]))
    
), "EndingOnHand",var _date= [Weekdate] var _t = FILTER( CALCULATETABLE('RawDataTable'),[Weekdate]=_date) return minx(_t,[EndingOnHand])  )

     

    Then we can get the result table like this:

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thank you Aniya Zhang! 

    Here is the report with Public sensitivity.. Let me know if you are able to access now else will share you the sample data.. 

    Sorry for the inconvenence!

     

    Sample for dynamic calculation 

     

    -Prab

    • v-yueyunzh-msft's avatar
      v-yueyunzh-msft
      Community Support
      3 years ago

      Hi , Anonymous 

      Thanks for your quick response!I download your pbix file and i can open it successfully!

      For your two needs , here are the steps you can refer to :
      (1)We can just modify your [IsInBuffer-check] measure like this:

      IsInBuffer-check = if(min(SummarizedTable[EndingOnHand])<=0 && min(SummarizedTable[Weekdate])<= [Today+Weeknum], "N","Y")

      Then we can get this:

      (2)For your second need , we can click "New Table" and enter this:

      Table 3 = ADDCOLUMNS( ADDCOLUMNS (
    SUMMARIZE (
        RawDataTable,RawDataTable[Filekey],RawDataTable[Item],RawDataTable[Itemkey],RawDataTable[location],RawDataTable[Productname],RawDataTable[Source]
    ),
    "Weekdate",  var _t =  CALCULATETABLE('RawDataTable')  var _t2 = FILTER(_t,[EndingOnHand]<=0)   return IF(MINX(_t2,[Weekdate])=BLANK() , MINX(_t,[Weekdate]) ,MINX(_t2,[Weekdate]))
    
), "EndingOnHand",var _date= [Weekdate] var _t = FILTER( CALCULATETABLE('RawDataTable'),[Weekdate]=_date) return minx(_t,[EndingOnHand])  )

       

      Then we can get the result table like this:

       

      Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

       

      Best Regards,

      Aniya Zhang

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Aniya Zhang,

         

        Thank you fro your help in providing the solution. 

        I will verfy the output and share you the result and accordingly accept the solution.

         

        Thanks Again!

        -Prab

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Community Support
    3 years ago

    Hi , Anonymous 

    Thanks for your sample pbix file , i download in my side but when i can not open it due to it has a sensitivity label for your pbix file.

     

    For your first need,you want to create a flag that changes dynamically based on user input. First of all, it must be clear that calculated columns cannot obtain user input values, because calculated columns are row contexts and cannot obtain user input at the report page level.So, for your first requirement, we can only use measure to dynamically obtain user input and return flag.

     

    For your second requirement, do you want to create a new table? Similarly, calculation tables cannot obtain user input to achieve dynamics.If you still want to display some data you want based on user input, you can only use measures to filter tables or calculate values.

     

    Finally, for your two requirements, can you provide some test data and give the correct values (in tabular form) that you want to get in the end?This would make the question more clear as I don't quite understand your second requirement！

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

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