Forum Discussion

ephramz's avatar
ephramz
Helper II
4 years ago
Solved

Help with project

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 val...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    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's avatar
ephramz
Helper II
4 years ago

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's avatar
Greg_Deckler
Community Champion
4 years ago

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's avatar
    ephramz
    Helper II
    4 years ago

    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's avatar
      Greg_Deckler
      Community Champion
      4 years ago

      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's avatar
        ephramz
        Helper II
        4 years ago

        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