Forum Discussion

gaikwadaa123's avatar
gaikwadaa123
Helper II
5 years ago
Solved

Slicer Selection based on the custom column

Hello, I have one table Name 'Calculations' which has below columns 

I am using 'Code' column as slicer. 

I have seond table 'DEPLoad' 

I would like to create the custom column in table Calculations table in such a way that whenever i select code '1', the 'Value' that belongs to the code will get substarcted from the 'Net load' column of the table 'DEPLaod'. whenever i select code '2', the 'Value' that belongs to the code will get substarcted from the 'Net load' column of the table 'DEPLaod'. Same for code 3. Same for the diffrenet combinations of the codes, 1,2  or 2,3 or 1,3 or 1,2,3 

Basically i want to subatstarct the'value' from the Net load based on what code or codes I select on the slicer. 

Can anyone help with the custom code. Really appreciate if you could address the issue based on my table names and columns. 

Thank you. 

 

Need Help
Show and Tell
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, gaikwadaa123 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Calculations:

     

    DEPLoad:

     

    You may create a measure as below.

    Result Measure = 
var tab = 
ADDCOLUMNS(
    'Calculations',
    "Re",
    CALCULATE(
        SUM(DEPLoad[Net Load]),
        FILTER(
            ALL(DEPLoad),
            [MeasureDate]=EARLIER('Calculations'[DateTimeSelection])
        )
    )-[Value]
)
return
SUMX(
    tab,
    [Re]
)

     

    Result:

     

    Best Regards

    Allan

     

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

5 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    Hello gaikwadaa123 

     

    are these two tables have a relationship? What you mean with "select code". You need this in a visual? If yes, you have to go for a measure and not for a new column.

     

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

    • gaikwadaa123's avatar
      gaikwadaa123
      Helper II
      5 years ago

      Hi The relationship between the tables is on the date column. "code" column is going to be my slicer. so the slicer will have three codes. by clicking any of them the visulas will change. 

      Can you help with the calculated column?

       

      • Jimmy801's avatar
        Jimmy801
        Community Champion
        5 years ago

        Hello gaikwadaa123 

         

        as I wrote already. Its not possible to use a selected value in a new column. So you have to go for a new measure and using SUM(DEPLoad[NetLoad])- SUM(Calculations[Value])

        Hope this helps


        If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
        Kudoes are nice too

        Have fun

        Jimmy

  • smpa01's avatar
    smpa01
    Community Champion
    5 years ago

    gaikwadaa123 the 'Value' that belongs to the code will get substarcted from the 'Net load' - e.g. what is the Net Load value by Code from DEPLoad table for the rows in  'Calculations' table. How do you intend to train the query to lookup for that info? Can you give some example /.logic?

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    Hi, gaikwadaa123 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Calculations:

     

    DEPLoad:

     

    You may create a measure as below.

    Result Measure = 
var tab = 
ADDCOLUMNS(
    'Calculations',
    "Re",
    CALCULATE(
        SUM(DEPLoad[Net Load]),
        FILTER(
            ALL(DEPLoad),
            [MeasureDate]=EARLIER('Calculations'[DateTimeSelection])
        )
    )-[Value]
)
return
SUMX(
    tab,
    [Re]
)

     

    Result:

     

    Best Regards

    Allan

     

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