cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
roncruiser
Helper IV
Helper IV

Convert value from measure to a Constant Value

Hi,

 

Not sure why this happens.  

I have a variable measure that gives me the expected value, but doesn't perform as expected when plugged into the main measure.

When I hardcode the same variable value, the main measure performs as expected.

I've been reading around the powerbi community and what I need from the variable is to convert the value to a constant.  Is this possible?

 

Here is the full Measure:

//This variable needs to be converted to a constant value.

VAR Variable1 = CALCULATE( sumx(maintable, maintable[Value]), Filter(All(maintable[position]), maintable[position] = 1), maintable[intensity]=0 )

 

 

RETURN

//The VAR from above is plugged into the measure below.  When Variable1 is instead hardcoded with the same fixed/contant //value the measure works perfectly.  Is there a workaround?

//In other words, remove the context from the calculated variable values so it's seen as a constant singular value.

if(or(SUM(maintable[position]) = 0 && sum(maintable[Value]) = Variable1,

(SUM(maintable[intensity]) = 0 && sum(maintable[Value]]) = Variable1)),

Sum(maintable[Value]) +1, Sum(maintable[Value]))

 

Thank You.

2 ACCEPTED SOLUTIONS
parry2k
Super User III
Super User III

@selimovd looking at the measure, it will not work because you are using only one column in ALL function and it will fail to filter Intensity column. Just my 2 cents.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Hi  @roncruiser ,

 

Modify your variable as below:

 

Variable1 =
SUMX (
    FILTER (
        ALL (maintable[position],maintable[intensity]),
        maintable[position] = 1
            && maintable[intensity] = 0
    ),
    maintable[Value]
)

 

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

View solution in original post

8 REPLIES 8
parry2k
Super User III
Super User III

@selimovd looking at the measure, it will not work because you are using only one column in ALL function and it will fail to filter Intensity column. Just my 2 cents.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

selimovd
Community Champion
Community Champion

Hey @parry2k ,

 

you're absolutely right. I Didn't test the function. I found the approach with CALCULATE and SUMX unusual, so I tried a quick fix. But you're right, like this it won't return a table.

 

Best regards

Denis

roncruiser
Helper IV
Helper IV

 

@selimovd 

@parry2k 

 

Here is the full Measure:

 

//This variable needs to be converted to a constant value.

 

VAR Variable1 = CALCULATE( sumx(maintable, maintable[Value]), Filter(All(maintable[position]), maintable[position] = 1), maintable[intensity]=0 )

 

RETURN

//The VAR from above is plugged into the measure below.  When Variable1 is instead hardcoded with the same fixed/contant value the measure works perfectly.  Is there a workaround?

 

if(or(SUM(maintable[position]) = 0 && sum(maintable[Value]) = Variable1,

(SUM(maintable[intensity]) = 0 && sum(maintable[Value]]) = Variable1)),

Sum(maintable[Value]) +1, Sum(maintable[Value]))

Hi  @roncruiser ,

 

Modify your variable as below:

 

Variable1 =
SUMX (
    FILTER (
        ALL (maintable[position],maintable[intensity]),
        maintable[position] = 1
            && maintable[intensity] = 0
    ),
    maintable[Value]
)

 

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

View solution in original post

Thank You.

selimovd
Community Champion
Community Champion

Hey @roncruiser ,

 

in general it's very uncommon to use SUMX within CALCULATE and also that you put some arguments in FILTER and some in the context of CALCULATE.

 

I would do the FILTER part in the SUMX:

Variable1 =
SUMX(
    FILTER(
        ALL( maintable[position] ),
        maintable[position] = 1 && maintable[intensity] = 0
    ),
    maintable[Value]
)

 

Can you check if that will result in the same value?

 

Then when you add the "Variable1" in your main measure, which of the results will be executed?

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 
selimovd
Community Champion
Community Champion

Hey @roncruiser ,

 

in Power BI there is nothing like a fixed value for a measure. 

A measure always has a filter context and is executed in that context. If you measure returns another value than when you integrate it into another value, then the context is different.

 

Can you show the main measure and how you integrate it? Also, please show some data and how you analyze (table, bar chart, etc.). That makes it easier to understand what contexts are working on a measure.

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 
parry2k
Super User III
Super User III

@roncruiser where is a variable here? Not sure I followed your post. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors