Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

How to use presentation variables in Power Bi when migrating report from OBI EE

Hi Team,

Im migrating one of my reports from OBI EE to Power Bi.

 

In OBI EE i have used presentation variables which capture boundary limit.

SUM(Table.Column BY People, Yearmonth) <= @{Var}{7}

This is the formula which is being used to calculate sum of all the values those are under 7.

 

How do i create the same scenario in Power BI?

In my research, i found that we can create a custom filter and use "SelectedValue" to fetch that value.

But im not sure how to do this, any help would be great!

Need Help

4 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Community Champion
    6 years ago

    Hi Anonymous ,

    If I understand your expected output, you might try something like this. Measure = SUMX(table,IF( table[yearmonth]<=7,table[People])). SUMX takes a table, so it knows which table, and then iterates over that table row by row using the expression on each row, and then sums the results of each row. So if we use an IF statement, it should sum all numbers of People.  You may need to have a relationship between two tables if there are more than one table. 
    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    We can create a measure and use it in table visual like following:

     

    Measure = 
CALCULATE ( SUM ( 'Table'[Column] ), 'Table'[Column] <= 7 )

     

    Or we can just create a calculated table to generate the result:

     

    SumTable = 
SUMMARIZECOLUMNS (
    'Table'[People],
    'Table'[YearMonth],
    "SumValue", CALCULATE ( SUM ( 'Table'[Column] ), 'Table'[Column] <= 7 )
)

     

    If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.


    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Suuu123's avatar
      Suuu123
      New Member
      3 years ago

      Hello,

      I have same problem.

      7 in this problem is a variable that means change when user change the report filter. 

      And I want to use this variable like a report filter. 

       

      Thanks

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,


    How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.