Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
prab
Microsoft Employee
Microsoft Employee

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))

 

Sample PBI Snip.png

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!

 

 

1 ACCEPTED SOLUTION

Hi , @prab 

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:

vyueyunzhmsft_0-1686707156995.png

(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:

vyueyunzhmsft_1-1686707245473.png

 

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

View solution in original post

4 REPLIES 4
prab
Microsoft Employee
Microsoft Employee

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

Hi , @prab 

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:

vyueyunzhmsft_0-1686707156995.png

(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:

vyueyunzhmsft_1-1686707245473.png

 

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

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
Community Support
Community Support

Hi , @prab 

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.

vyueyunzhmsft_0-1686620724357.png

 

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

 

 

 

 

 

 

 

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.