Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
mpwall5813
Frequent Visitor

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 example - If a parameter is selected as "all", I would like to see the sum of the entire column 'daysonjob'. If the parameter is selected as 'remove days off site,' I would like to see the sum of 'daysonjob' where 'laboronsite' = 'Y'.

 

mpwall5813_0-1708543671890.png

 

Essentially, I want the user to be able to select 'Days On Job' as all days the job has been active or only days where labor was actually on site.

 

Is this possible? From my memory, this can be easily achieved in Tableau, but I can't seem to figure out how to write a DAX measure with parameters.

 

Thanks in advance,

 

1 ACCEPTED SOLUTION
v-zhengdxu-msft
Community Support
Community Support

Hi @mpwall5813 

 

Here are two methods:

The first one is to use the field parameter:

I create a set of sample data:

vzhengdxumsft_0-1708572338884.png

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:

vzhengdxumsft_1-1708572432281.pngvzhengdxumsft_2-1708572447634.png

The result is as follow:

vzhengdxumsft_3-1708572482636.png

 

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:

vzhengdxumsft_4-1708572587214.png

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.

View solution in original post

3 REPLIES 3
v-zhengdxu-msft
Community Support
Community Support

Hi @mpwall5813 

 

Here are two methods:

The first one is to use the field parameter:

I create a set of sample data:

vzhengdxumsft_0-1708572338884.png

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:

vzhengdxumsft_1-1708572432281.pngvzhengdxumsft_2-1708572447634.png

The result is as follow:

vzhengdxumsft_3-1708572482636.png

 

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:

vzhengdxumsft_4-1708572587214.png

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.

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 )
lbendlin
Super User
Super User

yes, prepare separate measures and then add them all to the same field parameter.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.