Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Use Parameter to Slice Aggregate Measure?

Hello all -    Based off the below dataset, I have summed the 'daysonjob' column on my dashboard. Is there a way to filter or slice that aggregation based off a parameter within DAX?   For exampl...
aggregations
DAX
parameters
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous 

     

    Here are two methods:

    The first one is to use the field parameter:

    I create a set of sample data:

    Then add two measure:

    N = 
CALCULATE (
    SUM ( 'Table (2)'[daysonjob] ),
    FILTER ( ALLSELECTED ( 'Table (2)' ), 'Table (2)'[laboronsite] = "N" )
)
    Y = 
CALCULATE (
    SUM ( 'Table (2)'[daysonjob] ),
    FILTER ( ALLSELECTED ( 'Table (2)' ), 'Table (2)'[laboronsite] = "Y" )
)

    Add the two measure as parameter:

    The result is as follow:

     

    The second method:

    Create a new table:

    Table = {"Y","N"}

    Then add a measure:

    Measure = 
VAR _Newvalue = SELECTEDVALUE('Table'[Value])
VAR _labor = SWITCH(
    _Newvalue,
    "Y","Y",
    "N","N"
)
RETURN     
    CALCULATE (
        SUM ( 'Table (2)'[daysonjob] ),
        FILTER ( ALLSELECTED ( 'Table (2)' ), 'Table (2)'[laboronsite] = _labor )
    )

    Create a slicer with table[value] and a card with the measure:

    The result:

    Best Regards

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous 

 

Here are two methods:

The first one is to use the field parameter:

I create a set of sample data:

Then add two measure:

N = 
CALCULATE (
    SUM ( 'Table (2)'[daysonjob] ),
    FILTER ( ALLSELECTED ( 'Table (2)' ), 'Table (2)'[laboronsite] = "N" )
)
Y = 
CALCULATE (
    SUM ( 'Table (2)'[daysonjob] ),
    FILTER ( ALLSELECTED ( 'Table (2)' ), 'Table (2)'[laboronsite] = "Y" )
)

Add the two measure as parameter:

The result is as follow:

 

The second method:

Create a new table:

Table = {"Y","N"}

Then add a measure:

Measure = 
VAR _Newvalue = SELECTEDVALUE('Table'[Value])
VAR _labor = SWITCH(
    _Newvalue,
    "Y","Y",
    "N","N"
)
RETURN     
    CALCULATE (
        SUM ( 'Table (2)'[daysonjob] ),
        FILTER ( ALLSELECTED ( 'Table (2)' ), 'Table (2)'[laboronsite] = _labor )
    )

Create a slicer with table[value] and a card with the measure:

The result:

Best Regards

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    This is helpful, thank you! One more thing I did not explain. I need the aggregate measure to also act as the denominator in an equation. It appears I am not able to reference the Parameter in a DAX statement as a denominator. I created the below measure, and it seems to work as the denominator in a DAX statement.

     
    DOJ_PARAMETER_VALUE = 
VAR __SelectedValue =
    SELECTCOLUMNS (
        SUMMARIZE ( 'Days On Job Parameter', 'Days On Job Parameter'[Days On Job Parameter], 'Days On Job Parameter'[Days On Job Parameter Fields] ),
        'Days On Job Parameter'[Days On Job Parameter]
    )
RETURN IF ( COUNTROWS ( __SelectedValue ) = 1, __SelectedValue )