Forum Discussion

MigWare's avatar
MigWare
Icon for Helper I rankHelper I
6 years ago

Create a new column filtering values from column A and retrieve the correspondent value from columnB

Hi, I have the following two columns in a table, Product and Value, and I need to create a third column that would have the values of Value after being filtered by Column A. Example: New column wou...
Need Help
Icey's avatar
Icey
Icon for Community Support rankCommunity Support
6 years ago

Hi MigWare ,

 

I am not very clear what you want. Some possible requirements:

 

1. Create one column or measure to get the sum values of each product.

Column = CALCULATE ( SUM ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Product] ) )
Measure = CALCULATE ( SUM ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Product] ) )

 

2. You want to show the sum value of each product, expect for the selected product.

a. Create a table.

Product Filter = VALUES('Table'[Product])

b. Create a measure.

Condition = IF ( MAX ( 'Table'[Product] ) IN VALUES ( 'Product Filter'[Product] ), 1, 0 )

c. Put the condition measure on "Filters on this visual" of the visuals you want to filter.

 

BTW, .pbix file attached.

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • MigWare's avatar
    MigWare
    Icon for Helper I rankHelper I
    6 years ago

    thanks but that was not the aim of my question, trying to make it clear.

    What I have in the table:

    ProductValue
    GAS37,00
    GAS37,00
    GAS36,00
    GAS38,01
    GAS35,00
    FOOD37,00
    GAS37,00
    GAS37,00
    FOOD32,24
    FOOD34,29
    FOOD29,70
    FOOD32,05
    GAS32,62
    FOOD43,57
    GAS38,43
    GAS22,01
    FOOD46,09
    FOOD55,41
    FOOD26,64
    GAS37,57
    FOOD40,16
    GAS40,17
    FOOD31,55
    WATER40,38
    FOOD36,05
    FOOD35,56
    FOOD36,83
    FOOD35,69
    WATER36,28
    GAS38,00
    GAS59,51
    GAS38,12
    GAS37,52
    GAS54,82
    FOOD50,51
    FOOD52,44
    FOOD46,23
    FOOD53,00
    GAS40,53

     

    What I want to achieve:

     

    ProductValueObjective
    GAS37,000
    GAS37,000
    GAS36,000
    GAS38,010
    GAS35,000
    FOOD37,0037,00
    GAS37,000
    GAS37,000
    FOOD32,2432,24
    FOOD34,2934,29
    FOOD29,7029,70
    FOOD32,0532,05
    GAS32,620
    FOOD43,5743,57
    GAS38,430
    GAS22,010
    FOOD46,0946,09
    FOOD55,4155,41
    FOOD26,6426,64
    GAS37,570
    FOOD40,1640,16
    GAS40,170
    FOOD31,5531,55
    WATER40,3840,38
    FOOD36,0536,05
    FOOD35,5635,56
    FOOD36,8336,83
    FOOD35,6935,69
    WATER36,2836,28
    GAS38,000
    GAS59,510
    GAS38,120
    GAS37,520
    GAS54,820
    FOOD50,5150,51
    FOOD52,4452,44
    FOOD46,2346,23
    FOOD53,0053,00
    GAS40,530

     

    • edhans's avatar
      edhans
      Icon for Community Champion rankCommunity Champion
      6 years ago

      use an IF function.

      It is better to do this in Power Query, but I'll give both PQ and DAX:

      Power Query:

      Add a conditional column that will essentially generate this formula:

      if [Product] = "GAS" then 0 else [Value]

      It will return this table

      If you want a calculated column (not advised), use this formula:

      Objective = 
IF(
    'Table'[Product] = "GAS",
    0,
    'Table'[Value]
)

       

      In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
      Calculated Columns vs Measures in DAX
      Calculated Columns and Measures in DAX
      Storage differences between calculated columns and calculated tables
      Creating a Dynamic Date Table in Power Query

       

      • MigWare's avatar
        MigWare
        Icon for Helper I rankHelper I
        6 years ago

        thanks, that was the objective. One question, if i want to add another product on the DAX expression

        Objective = 
IF(
    'Table'[Product] = "GAS",
    0,
    'Table'[Value]
)

        , is that possible? I´ve tried to use 'Table'[Product] = {"GAS","FOOD"} but i get an error saying a table of multiple values was supplied where a single value was expected