Forum Discussion

AleksandrG's avatar
AleksandrG
Helper I
3 years ago
Solved

DAX. Filtering the calculation through a parameter.

Guys, hello! Please help me to solve the following problem. I want to calculate the lost profit only for products whose profit is less than the specified value in the parameter. I created a meas...
Need Help
Show and Tell
  • AleksandrG's avatar
    AleksandrG
    3 years ago

    here is the solution 🙂

    VAR _table =
        FILTER (
            ADDCOLUMNS (
                Assortment4,
                "Persent", CALCULATE ( [% Profit], ALLSELECTED ( 'Calendar' ) ),
                "Profit", CALCULATE ( [Profit], ALLSELECTED ( 'Calendar' ) ),
                "SalesQty", CALCULATE ( [Sales Qty], ALLSELECTED ( 'Calendar' ) ),
                "SalesPerDay", CALCULATE ( [Sales Per Day], ALLSELECTED ( 'Calendar' ) )
            ),
            [Persent] > '% Profit'[Meaning % Profit]
        )
    RETURN
        SUMX (
            _table,
            IF (
                [Remainder] = 0,
                DIVIDE ( [Profit], [SalesQty], 0 ) * [SalesPerDay]
            )
        )

     

amitchandak's avatar
amitchandak
Super User
3 years ago

AleksandrG , to filter a measure you need to force a group by. or use measure in the visual level filter

 

example

suxm(filter(values(Table[Name]), [percent] < selectedvalue(whatif[value]) ), [Measure])

AleksandrG's avatar
AleksandrG
Helper I
3 years ago

Hello)
Many thanks for the advice.
I'd really like to do this functionally - via Create Parameter if possible)
But is there really no way to make a filter through Create parameter - Numeric parameter?

It seems that everything is obvious, at first glance, how to implement it.)) But for some reason it does not work.
I have already started trying through creating a DAX virtual table.
Here's what happens:
If I create a virtual table without the FILTER function, then everything works as it should.

 

VAR _table =
    ADDCOLUMNS (
        Assortment4,
        "Persent", [% Profit],
        "Profit", CALCULATE ( [Profit], ALLSELECTED ( 'Calendar' ) ),
        "SalesQty", CALCULATE ( [Sales Qty], ALLSELECTED ( 'Calendar' ) ),
        "SalesPerDay", CALCULATE ( [Sales Per Day], ALLSELECTED ( 'Calendar' ) )
    )
RETURN
    SUMX (
        _table,
        IF ( [Remainder] = 0, DIVIDE ( [Profit], [SalesQty], 0 ) * [SalesPerDay] )
    )


But as soon as I start filtering the table through the created parameter, the data from the chart disappears.

 

VAR _table =
    FILTER (
        ADDCOLUMNS (
            Assortment4,
            "Persent", [% Profit],
            "Profit", CALCULATE ( [Profit], ALLSELECTED ( 'Calendar' ) ),
            "SalesQty", CALCULATE ( [Sales Qty], ALLSELECTED ( 'Calendar' ) ),
            "SalesPerDay", CALCULATE ( [Sales Per Day], ALLSELECTED ( 'Calendar' ) )
        ),
        [Persent] > '% Profit'[Meaning % Profit]
    )
RETURN
    SUMX (
        _table,
        IF (
            [Remainder] = 0,
            DIVIDE ( [Profit], [SalesQty], 0 ) * [SalesPerDay]
        )
    )
 
What am I doing wrong?) How to fix this?
  • AleksandrG's avatar
    AleksandrG
    Helper I
    3 years ago

    here is the solution 🙂

    VAR _table =
        FILTER (
            ADDCOLUMNS (
                Assortment4,
                "Persent", CALCULATE ( [% Profit], ALLSELECTED ( 'Calendar' ) ),
                "Profit", CALCULATE ( [Profit], ALLSELECTED ( 'Calendar' ) ),
                "SalesQty", CALCULATE ( [Sales Qty], ALLSELECTED ( 'Calendar' ) ),
                "SalesPerDay", CALCULATE ( [Sales Per Day], ALLSELECTED ( 'Calendar' ) )
            ),
            [Persent] > '% Profit'[Meaning % Profit]
        )
    RETURN
        SUMX (
            _table,
            IF (
                [Remainder] = 0,
                DIVIDE ( [Profit], [SalesQty], 0 ) * [SalesPerDay]
            )
        )