Unable to add slicer value in filter to count rows.

[Anonymous]

How to use slicer value in calculated column??

It's actually working fine when we use directly value in filter but unable to use silcer value .

***Not working***

Data Match = SWITCH('SF vs Bank'[Field Name],"Bank Account Number (BBAN)",CALCULATE(COUNTROWS('SF~Bank'),FILTER('SF~Bank','SF~Bank'[Comments]="Match"),'SF~Bank'[Bank Name]=ALLSELECTED(Lstvalues[LstValues])))

***Working***

Data Match = SWITCH('SF vs Bank'[Field Name],"Bank Account Number (BBAN)",CALCULATE(COUNTROWS('SF~Bank'),FILTER('SF~Bank','SF~Bank'[Comments]="Match"),'SF~Bank'[Bank Name]="CITI")))

i have used slicer named as Lstvalues 

Help to use slicer value in filter.

[Anonymous]

Hi @Anonymous,

To my knowledge,both calculated column and calculated table are static,which means they could not be dynamically changed by slicer. 

As @amitchandak said, please create measure instead.

I have reproduced some data sample and rewrited the wrong expression, please kindly take a look at the formula and the final output shown below:

Measure =
VAR _all =
    ALLSELECTED ( 'Lstvalues'[LstValues] )
VAR _count =
    CALCULATE (
        COUNTROWS ( 'SF~Bank' ),
        FILTER (
            'SF~Bank',
            'SF~Bank'[Bank Name]
                IN _all
                && 'SF~Bank'[Comments] = "Match"
        )
    )
RETURN
    SWITCH (
        MAX ( 'SF vs Bank'[Field Name] ),
        "Bank Account Number (BBAN)", _count
    )

 

You could check my pbix file here.

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

[Anonymous]

Hi Eyelyn,

Thankyou so much for the support, It worked. But Need 1 More Help!!!

 Also, Need to add one more silcer for selecting month to display month wise data,.

In "SF~Bank" table it having one more column named as month, values as (ie, Sept, Oct, Dec). If User selected from month as "sept" and to month as "Dec" then its should count the values which falls under sept to dec.

Thanks in advance 🙂

[Anonymous · Accepted solution]

Hi @Anonymous ,

In order to better identify the sequence of the selected months, it is recommended to add MonthNumber column.

Please follow these steps:

1. Add MonthNumber column to SF~Bank table:

2. Use the following formula to create a Month table for slicer:

forSlicer =
DISTINCT (
    SELECTCOLUMNS (
        CALENDAR ( "2020/1/1", "2020/12/31" ),
        "Month", FORMAT ( [Date], "mmm" ),
        "MonthNumber", MONTH ( [Date] )
    )
)

3. Rewrite the measure:

Measure =
VAR _all =
    ALLSELECTED ( 'Lstvalues'[LstValues] )
VAR _max =
    MAXX (
        FILTER (
            'forSlicer',
            'forSlicer'[MonthNumber] = MAX ( 'forSlicer'[MonthNumber] )
        ),
        [MonthNumber]
    )
VAR _min =
    MINX (
        FILTER (
            'forSlicer',
            'forSlicer'[MonthNumber] = MIN ( 'forSlicer'[MonthNumber] )
        ),
        [MonthNumber]
    )
VAR _count =
    CALCULATE (
        COUNTROWS ( 'SF~Bank' ),
        FILTER (
            'SF~Bank',
            'SF~Bank'[Bank Name]
                IN _all
                && 'SF~Bank'[Comments] = "Match"
                && 'SF~Bank'[MonthNumber] >= _min
                && 'SF~Bank'[MonthNumber] <= _max
        )
    )
RETURN
    SWITCH (
        MAX ( 'SF vs Bank'[Field Name] ),
        "Bank Account Number (BBAN)", _count
    )

The final output is shown below:

Please kindly take a look at the pbix file here.

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

[amitchandak]

@Anonymous , slicer you can use in  measure. You can create a measure, if needed force a row context using values or summarize

examples

sumx(Values(Table[ID]),[measure])

Sumx(summarize(Table, Table[ID], Table[Date], "_1", [measure]),[_1])

[amitchandak]

@Anonymous , You can not use slicer/filter values in a column.

[Anonymous]

Thankyou.

Is there any other options to include slicer. I'm using two tables. One to retreive count of columns based on condition in another table,

1st Table - Customised calculated column table created by me to retreive count from second table

2nd Table - Raw Data