Forum Discussion

clem312's avatar
clem312
Icon for Resolver I rankResolver I
5 years ago
Solved

ALL respecting the selection

Hi,

 

I have a generated Table with a ALL filter like this :

TGWbis_m_Year_FR = 
ADDCOLUMNS(
GENERATE('model',
SUMMARIZE(FILTER(TGW, TGW[COUNTRY]="FR"), annee[Annee_lib], code[code_lib])
)
,
"@Note",
    IF(
        'model'[lib_model]="Segment Average",
        CALCULATE(
            [%code_Year_FR],
            ALL('model')
        ),
        CALCULATE([%code_Year_FR])
    )
,
    "@Rank",
    IF(
        'model'[lib_model]="Segment Average",
        RANKX(
            VALUES(code[code_lib]),
            CALCULATE(
                [%code_Year_FR],
                ALL('model')
            ),
            ,
            DESC
        ),
        RANKX(
            VALUES(code[code_lib]),
            CALCULATE(
                [%code_Year_FR]+(VALUES(code[code_id])/10000)),
            ,
            DESC
            ,
            DENSE
        )
    )

)

I need to FILTER a part of this ALL(model) for the "Segment Average"

BUT I cannot create as much table as I need to filter.

So... I need to keep this ALL and make a selection in my dashboard.

Is there a function that would keep ALL(model) but respect the slicer selection ?

Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    5 years ago

    Hi clem312 

     

    You may consider using measure instead of percent.first, inorder to no interact with the visual, another table is necessary,creating it with the next formula

    Table = VALUES('Q72bis_m'[model_segment])

     

    I used this formula, which you can refer to.

    Percent Measure =
VAR _S =
    SELECTEDVALUE( 'Table'[model_segment] )
VAR _C =
    FIRSTNONBLANK( 'Q72bis_m'[Q72_lib], 0 )
VAR _P =
    AVERAGEX(
        FILTER(
            ALL( 'Q72bis_m' ),
            [model_segment] = _S
                && [@Rank] = SELECTEDVALUE( Q72bis_m[@Rank] )
                && [Q72_lib] = _C
        ),
        'Q72bis_m'[@Note]
    )
RETURN
    IF(
        _s = BLANK(),
        SUM( 'Q72bis_m'[@Note] ),
        IF( SELECTEDVALUE( 'Q72bis_m'[model_lib] ) = "Segment Average", _P, BLANK() )
    )

     

     I put your pbix file in the attachment you can reference.

     

    Best Regards

    Community Support Team _ chenwu zhu

     

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

3 Replies

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi clem312 

     

    You may consider using measure instead of percent.first, inorder to no interact with the visual, another table is necessary,creating it with the next formula

    Table = VALUES('Q72bis_m'[model_segment])

     

    I used this formula, which you can refer to.

    Percent Measure =
VAR _S =
    SELECTEDVALUE( 'Table'[model_segment] )
VAR _C =
    FIRSTNONBLANK( 'Q72bis_m'[Q72_lib], 0 )
VAR _P =
    AVERAGEX(
        FILTER(
            ALL( 'Q72bis_m' ),
            [model_segment] = _S
                && [@Rank] = SELECTEDVALUE( Q72bis_m[@Rank] )
                && [Q72_lib] = _C
        ),
        'Q72bis_m'[@Note]
    )
RETURN
    IF(
        _s = BLANK(),
        SUM( 'Q72bis_m'[@Note] ),
        IF( SELECTEDVALUE( 'Q72bis_m'[model_lib] ) = "Segment Average", _P, BLANK() )
    )

     

     I put your pbix file in the attachment you can reference.

     

    Best Regards

    Community Support Team _ chenwu zhu

     

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

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    clem312 

    As per my understanding of your question, Once you have created a table and added it to the model, it is fixed, any selection from your report/dashboard cannot be incorporated into it. If you can elaborate with an example of attaching a dummy file, it will make it easy.