Create Ability to Make Column References Variables

[charleshale]

It would be great to have a feature that allows a column reference to be a variable to make it easier to bulk edit measures.   For example, let's talk renewal rates.    A classic case for renewal rates is to examine them at the customer level but also at the parent company level.      ie Audible might be a customer but the parent company would be Amazon.   KLM might be a customer but the parent company would be Air France KLM.

Being able to make an entire column a variable would allow for easier bulk measure changes because one could simply switch the column being measured (ie Customer vs Parent) with a variable like VARCOL = Table1[Customer]    or Table1[Parent]  and all the downstream measures for things could point to VARCOL instead of the column at hand.

[RandyPgh · Accepted solution]

You could put this into ideas.powerbi.com and see if it gets upvoted, but if I understand your needs, there is a workaround.

First, create a table with the on column containing your values "Child Company" and "Parent Company". Let's call the table InterestLevel and the column Level.

Next, put a slicer onto the report, and assign the value of the slicer 'InterestLevel'[Level].

Next, in your measures, you would need to prefix all the code with a variable something like the following (untested code):

VAR ColumnOfInterest = 

    IF( HASONEVALUE( 'InterestLevel'[Level] ), 

        IF( VALUES( 'InterestLevel'[Level] = "Child Company",

             VALUES( 'CompanyTable'[Child]),    // use the child data column

             VALUES('CompanyTable'[Parent])    // use the parent data column

          ),

       VALUES('CompanyTable'[Child])     // default to the child column

    )

Then your measures would need to filter their results by the ColumnOfInterest.

[MFelix]

Hi  @charleshale ,

Not really sure if I understand your question but this is possible if you make on your measure an additional step based on a table for saving the columns you need to change.

If you have a table with for example:

Column Selection

Parent

Customer

And then you make your measure with a formula similar to the one below you will get the calculation change accordingly:

Measures calculation =
SWITCH (
    SELECTEDVALUE ( 'Measures_table'[Column Selection] );
    "Parent"; CALCULATE ( SUM ( Table[Column] ); ALLSELECTED ( Table[Parent Column] ) );
    "Customer"; CALCULATE ( SUM ( Table[Column] ); ALLSELECTED ( Table[Customer Column] ) );
    BLANK ()
)

Other option can also to have a measure similar to the one below that will not need the additonal table and is used based on context for example with matrix tables or chart with drill down option:

Measures calculation =
IF (
    HASONEVALUE ( Table[Customer Column] );
    CALCULATE ( SUM ( Table[Column] ); ALLSELECTED ( Table[Customer Column] ) );
    CALCULATE ( SUM ( Table[Column] ); ALLSELECTED ( Table[Parent Column] ) )
)

So depending on your needs and visualizations believe that is possible to achieve what you are refering.

If I was not clear can you please share an example with data and expected results?

Regards,

MFelix

[charleshale]

@MFelix @RandyPgh 

Hi.   Here's a test file in PBIX.

I'm failing with your workaround (as least as I am trying to implement it) because the text I am trying to tokenize is the pure parameter (ie column name) rather than the function (ie  VALUES(parameter), SUM(parameter) etc.).

To be specific, at the most basic level in the case below, ideally I could make a variable column called VAR_COL and then switch VAR_COL to whatever I want to measure (ie Table1[AcctNamne_Parent] or Table1[AcctName_Sub]).

|1YrChrn|$(Reductions)*^ = SUMX(FILTER(SUMMARIZE(
    VALUES(
       VAR_COL                  
        ), 
         VAR_COL             
      , "Revs", [$RevsUSDE^] 
      , "PriorPeriodRevs", [|1YrChrn|$PriorPerLstYr**] )
      , [PriorPeriodRevs] <> 0 && [Revs] <= [PriorPeriodRevs] && [Revs]<>0)
            , [Revs]-[PriorPeriodRevs]
)

To take this one step further, since renewal rate measures like AddOns, Reductions, Cancellations, New Customers are different only in the logic expression at the end of the SUMMMARIZE function, nirvana would be to tokenize the whole darn thing!!  As in the following:

|1YrChrn|$(Reductions)*^ = TOKENIZATION_CHURN
      , [PriorPeriodRevs] <> 0 && [Revs] <= [PriorPeriodRevs] && [Revs]<>0)
            , [Revs]-[PriorPeriodRevs]
)

|1YrChrn|$(AddOns)*^ = TOKENIZATION_CHURN
     , [PriorPeriodRevs] <> 0  &&  [Revs]>0  &&  [Revs] > [PriorPeriodRevs])
            , [Revs]-[PriorPeriodRevs]
)

|1YrChrn|$Lost full cancels*^ = TOKENIZATION_CHURN
       ,[PriorRevs] <>0 && [Revs]=0) , -[PriorRevs]
)
    

I am definitely falling hard for PowerBI.   Thank you for looking at my perhaps silly thoughts here.

[MFelix · Accepted solution]

HI @charleshale ,

To what I can understand you want to make a Variable column that will change the values based on a selection correct?

This cannot occur in PBI on the way you are describing, since the data will not change accordingly to selection on your slicers,  filters, etc. You can make a parameter to change values of a column however that would need you to make a new refresh of the data every time you need to make the calculation based on a different value.

Looking at the code you have written you need to make basically a measure (or several) that will have the several options based on the calculation you need and then when you make the selection of the column you want to refer the information is recalculated.

On your case you would need to have a function (or parts of functions) as much as columns you need to calculate:

Further more to what I can understand you wnat to have dinamic changes on the visuals. Check the answer to this post because I think will have part of the solution you need.

https://community.powerbi.com/t5/Desktop/Dynamic-change-in-X-Axis/m-p/87174#M36525 

If you need any further assistance please provide some sample data and expected results.

Regards.

MFelix

[RandyPgh · Accepted solution]

You could put this into ideas.powerbi.com and see if it gets upvoted, but if I understand your needs, there is a workaround.

First, create a table with the on column containing your values "Child Company" and "Parent Company". Let's call the table InterestLevel and the column Level.

Next, put a slicer onto the report, and assign the value of the slicer 'InterestLevel'[Level].

Next, in your measures, you would need to prefix all the code with a variable something like the following (untested code):

VAR ColumnOfInterest = 

    IF( HASONEVALUE( 'InterestLevel'[Level] ), 

        IF( VALUES( 'InterestLevel'[Level] = "Child Company",

             VALUES( 'CompanyTable'[Child]),    // use the child data column

             VALUES('CompanyTable'[Parent])    // use the parent data column

          ),

       VALUES('CompanyTable'[Child])     // default to the child column

    )

Then your measures would need to filter their results by the ColumnOfInterest.

[charleshale]

This would be the work-around.   Thank you.   Trying it out now.