Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Select a column by parameter

Hi everyone,   I am new to Power BI and exploring the dashboard. I have created a measure to calculate the cumulative value.   Cumulative Freezer= CALCULATE (     SUM ( 'Business Plan'[Freezer]...
  • Eric_Zhang's avatar
    Eric_Zhang
    8 years ago
    Anonymous wrote:

    Thanks for the disconnected slicer information. But I think the main difference is that I hope to filter by columns but the disconnected slicer filters data by rows. Still thanks for your information.

    Anonymous

    In my opinion, a better practice for your case is to unpivot the columns and slice the unpivoted column.

     

    If the way in your original post is still preferable, you could try to create measure as

     

    Cumulative Freezer =
SWITCH (
    TRUE (),
    HASONEVALUE ( ParameterTable[Parameter] )
        && ISFILTERED ( ParameterTable[Parameter] )
        && LASTNONBLANK ( ParameterTable[Parameter], "" ) = "Freezer", CALCULATE (
        SUM ( 'Business Plan'[Freezer] ),
        FILTER (
            ALL ( 'Business Plan'[Period] ),
            'Business Plan'[Period] <= MAX ( 'Business Plan'[Period] )
        )
    ),
    HASONEVALUE ( ParameterTable[Parameter] )
        && ISFILTERED ( ParameterTable[Parameter] )
        && LASTNONBLANK ( ParameterTable[Parameter], "" ) = "Washing machine", CALCULATE (
        SUM ( 'Business Plan'[Washing machine] ),
        FILTER (
            ALL ( 'Business Plan'[Period] ),
            'Business Plan'[Period] <= MAX ( 'Business Plan'[Period] )
        )
    ),
    0
)
GilbertQ's avatar
GilbertQ
Super User
8 years ago

Hi Anonymous

 

You cannot pass a column name into a DAX measure as far as I am aware. 

 

You can pass values within a column to a DAX measure though.

 

Do you have an example of what you are trying to achieve?

Anonymous's avatar
Anonymous
Not applicable
8 years ago

 

Here is a sample of my data. So far I created a measure for column "Freezer".

Cumulative Freezer=
CALCULATE (
    SUM ( 'Business Plan'[Freezer] ),
    FILTER (
        ALL ( 'Business Plan'[Period] ),
        'Business Plan'[Period] <= MAX ( 'Business Plan'[Period] )
    )
)

I hope that I can have different measure for different columns using parameter or other method so that I do not need to create new measure for every single column. A sample chart is below:

 

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thanks for the disconnected slicer information. But I think the main difference is that I hope to filter by columns but the disconnected slicer filters data by rows. Still thanks for your information.

      • Eric_Zhang's avatar
        Eric_Zhang
        Microsoft Employee
        8 years ago
        Anonymous wrote:

        Thanks for the disconnected slicer information. But I think the main difference is that I hope to filter by columns but the disconnected slicer filters data by rows. Still thanks for your information.

        Anonymous

        In my opinion, a better practice for your case is to unpivot the columns and slice the unpivoted column.

         

        If the way in your original post is still preferable, you could try to create measure as

         

        Cumulative Freezer =
SWITCH (
    TRUE (),
    HASONEVALUE ( ParameterTable[Parameter] )
        && ISFILTERED ( ParameterTable[Parameter] )
        && LASTNONBLANK ( ParameterTable[Parameter], "" ) = "Freezer", CALCULATE (
        SUM ( 'Business Plan'[Freezer] ),
        FILTER (
            ALL ( 'Business Plan'[Period] ),
            'Business Plan'[Period] <= MAX ( 'Business Plan'[Period] )
        )
    ),
    HASONEVALUE ( ParameterTable[Parameter] )
        && ISFILTERED ( ParameterTable[Parameter] )
        && LASTNONBLANK ( ParameterTable[Parameter], "" ) = "Washing machine", CALCULATE (
        SUM ( 'Business Plan'[Washing machine] ),
        FILTER (
            ALL ( 'Business Plan'[Period] ),
            'Business Plan'[Period] <= MAX ( 'Business Plan'[Period] )
        )
    ),
    0
)