Help with project

[ephramz]

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

[Greg_Deckler]

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

[ephramz]

Hi is there another way to do it, because my powerbi report keep says "not enough memory" is there another way to optimize this ?

[Greg_Deckler]

@ephramz Maybe:

Column = 
  VAR __Package = 'Table2'[Package]
  VAR __Arising = RELATED('Table1'[Arising])
  VAR __Delta = RELATED('Table1'[Delta])
RETURN
  IF(__Arising*__Delta <= 0,"Negative","Positive")

[ephramz]

@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

[Greg_Deckler]

@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.

[ephramz]

@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

[Greg_Deckler]

@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.

[ephramz]

@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"

[Greg_Deckler]

@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...

[ephramz]

How do i create a disconnected table with all the values of the "Arising * Delta" values @Greg_Deckler 

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

[ephramz]

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 

[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.

[ephramz]

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 

[Greg_Deckler · Accepted 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.