Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a measure that calculates Arising*Delta, which results blank if no data.
I want to create a new column in Table 2 called Status such if the measure Arising*Delta is a positive value , returns positive, if is negative return negative, if is blank, returns blanks and if result is zero return zero.
Regards,
ephramz
Solved! Go to Solution.
@ephramz That's because you have some other measure that is returning a value so the row is going to get displayed. If you don't want that to happen you can basically take the same exact formula in a new measure that returns 1 or 0. So:
Selector =
VAR __WhatToShow = SELECTEDVALUE('Table31a'[Column])
VAR __Value = MAX([Arising]) * MAX([Delta])
RETURN
SWITCH(__WhatToShow,
"positive",IF(__Value>0,1,0),
"negative",IF(__Value<0,1,0),
"zero",IF(__Value=0,1,0),
1
)
You use the Filter pane to filter on this measure is 1. This is called a Complex Selector. The Complex Selector - Microsoft Power BI Community
I feel like I've solved 12 problems in one thread.
@ephramz Try:
Column =
VAR __Package = 'Table2'[Package]
VAR __Arising = MAXX(FILTER('Table1',[Package] = __Package),[Arising])
VAR __Delta = MAXX(FILTER('Table1',[Package] = __Package),[Delta])
RETURN
IF(__Arising*__Delta <= 0,"Negative","Positive")
Hi is there another way to do it, because my powerbi report keep says "not enough memory" is there another way to optimize this ?
@ephramz Maybe:
Column =
VAR __Package = 'Table2'[Package]
VAR __Arising = RELATED('Table1'[Arising])
VAR __Delta = RELATED('Table1'[Delta])
RETURN
IF(__Arising*__Delta <= 0,"Negative","Positive")
@Greg_Deckler
the two tables are related by strategy and package (which are column values)
Arising is a measure and delta is also a measure.
And the (Arising*Delta) is the name of the measure
@ephramz So do you want an actual calculated column or do you want a measure that is a column in a table visual? Because measures and calculated columns do not tend to mix very well most of the time.
@Greg_Deckler
Okay, maybe let me explain myself again. Due to sensitivity of my data, i'll try to be as clear as possible, with some "sample" examples of the data.
I have two table and a seperate table (with all my measures)
Table 1: (Name: ENGG_VW_TRM)
Strategy Package and other columns
AA 1
AA 2
AC 3
Table 2: (Name: Repair Cap List)
Strategy Package and other column
AA 1
AA 2
AB 3
Both table are related according to the strategy and package column,
Table 3 Difference Impact (With all my measures)
"Arising" measure - Which is calculated based on table 1 "other columns values"
"Delta" measure Which is calculated based on table 1 "other columns values"
"Arising * Delta" measure the product of arising measure * delta measure
Based on the "Arising*Delta|" measure, it will generate a series of values which includes positive and negative values.
How do i create a column in Table 2 such that it will give me the corresponding "Arising*Delta" value because i need it to do a dynamic slicer for my report. Hopefully this makes things clear. Cheers
@ephramz You cannot create a dynamic calculated column in Table 2 based upon user inputs because calculated columns are only calculated when data is refreshed, not based upon user interaction.
@Greg_Deckler
Okay, then based on my measure "Arising* Delta" measure which has positive and negative values, how can i create a slicer than can show only "positive values", "negative values", "blank values" and "values that are zero"
@ephramz You would use the disconnected table trick. In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...
How do i create a disconnected table with all the values of the "Arising * Delta" values @Greg_Deckler
@ephramz Nope. You don't do that. You create an Enter Data query with these values for example:
Column
positive
negative
blank
zero
Then you create a measure like this:
Measure to Show =
VAR __WhatToShow = SELECTEDVALUE('DisconnectedSlicerTable'[Column])
VAR __Value = [Arising] * [Delta]
RETURN
SWITCH(__WhatToShow,
"positive",IF(__Value>0,__Value,BLANK()),
"negative",IF(__Value<0,__Value,BLANK()),
"zero",IF(__Value=0,__Value,BLANK())
)
I believe this measure works, but it does not relate back to the strategy and package, hence when i use this measure in the table visual, no value is shown @Greg_Deckler
@ephramz Not sure if that is the reason or if you just didn't have anything selected in your slicer. I modified this to be:
Measure to Show =
VAR __WhatToShow = SELECTEDVALUE('Table31a'[Column])
VAR __Value = MAX([Arising]) * MAX([Delta])
RETURN
SWITCH(__WhatToShow,
"positive",IF(__Value>0,__Value,BLANK()),
"negative",IF(__Value<0,__Value,BLANK()),
"zero",IF(__Value=0,__Value,BLANK()),
__Value
)
You can substitute your measures for the aggregations of Arising and Delta. See Page 8 of attached PBIX below sig.
In my table visual, when i press the slicer, it shows me the "negative" values. but it doesnt remvoe the rows that arent negative @Greg_Deckler
@ephramz That's because you have some other measure that is returning a value so the row is going to get displayed. If you don't want that to happen you can basically take the same exact formula in a new measure that returns 1 or 0. So:
Selector =
VAR __WhatToShow = SELECTEDVALUE('Table31a'[Column])
VAR __Value = MAX([Arising]) * MAX([Delta])
RETURN
SWITCH(__WhatToShow,
"positive",IF(__Value>0,1,0),
"negative",IF(__Value<0,1,0),
"zero",IF(__Value=0,1,0),
1
)
You use the Filter pane to filter on this measure is 1. This is called a Complex Selector. The Complex Selector - Microsoft Power BI Community
I feel like I've solved 12 problems in one thread.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |