Forum Discussion

cashewNut's avatar
cashewNut
Frequent Visitor
2 years ago
Solved

Controlling filter context in percentile calculation

I am struggling to control different filter context transitions within a complex dax measure. I am trying to compute the median duration for a certain percentile of events, where that percentile is p...
DAX
  • cashewNut's avatar
    cashewNut
    2 years ago

    Thanks Anonymous ! I am trying to avoid specifying filters directly in the query. I've ended up getting the query to work in production where I have a relationship to the filtering tables ('Table1') in this example, using a combination of PERCENTILE.INC(), KEEPFILTERS(), and ALLACCEPT()

    VAR _percentile =
    ADDCOLUMNS (
    FILTER (
    'Table',
    "@target",
    CALCULATE (
    MINX (
    FILTER (
    'Table',
    [event_order]
    >= PERCENTILEX.INC (
    VALUES ( 'Table'[event_order] ),
    'Table'[event_order],
    _percentile
    )
    ),
    [event_order]
    ),
    ALLEXCEPT ( 'Table', 'Table[opportunity_id] ),
    KEEPFILTERS ( 'TABLE1' ),
    )
    )
    //calculate the median duration for records in a result set limited to the ordering targets
    VAR _result =
    MEDIANX (
    FILTER (
    _percentile ,
    [_event_order] = [@target]
    ),
    [Duration]
    )
    RETURN
    _result
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi cashewNut ,

 

Try to modify your formula like below:

25% Duration (Adjusted) = 
VAR _percentile = 0.25
VAR _filteredTable = FILTER(ALLSELECTED('Table'), [status] = "Active (Not Recruiting)")
VAR _partitionedTable = 
    ADDCOLUMNS (
        _filteredTable,
        "@target",
            CEILING(
                CALCULATE (
                    MAX ( 'Table'[event_order]),
                    ALLEXCEPT('Table', 'Table'[opportunity_id])
                ) * _percentile, 
            1)
    )
VAR _result = 
    MEDIANX(
        FILTER (
            _partitionedTable,
            [event_order] = [@target]
        ), 
        [duration]
    )
RETURN _result

 

Best Regards,
Adamk Kong

 

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

cashewNut's avatar
cashewNut
Frequent Visitor
2 years ago

Thanks Anonymous ! I am trying to avoid specifying filters directly in the query. I've ended up getting the query to work in production where I have a relationship to the filtering tables ('Table1') in this example, using a combination of PERCENTILE.INC(), KEEPFILTERS(), and ALLACCEPT()

VAR _percentile =
ADDCOLUMNS (
FILTER (
'Table',
"@target",
CALCULATE (
MINX (
FILTER (
'Table',
[event_order]
>= PERCENTILEX.INC (
VALUES ( 'Table'[event_order] ),
'Table'[event_order],
_percentile
)
),
[event_order]
),
ALLEXCEPT ( 'Table', 'Table[opportunity_id] ),
KEEPFILTERS ( 'TABLE1' ),
)
)
//calculate the median duration for records in a result set limited to the ordering targets
VAR _result =
MEDIANX (
FILTER (
_percentile ,
[_event_order] = [@target]
),
[Duration]
)
RETURN
_result