Quick about filters

[Anonymous]

Hi guys, difficult and confusing ask, I hope you can help?

Client wants to set up targets at different levels, as examples:

	Platform	Line of Business	Product	KPI	Value
1	Facebook			Cost per click	16
2	Facebook	Retail		Cost per click	14
3	Facebook	Corporate	Cars	Cost per click	12
4	Facebook	Corporate	Cars	Cost per registration	10

For example in table above, when filter on Facebook to to show cost per click as $16

Separately, they might want to show Retail Cost per click as $14 when filter for Facebook and Line of business = retail.

Separately, they might want to show Corporate Cars as Cost per click as $12 with Cost pre registration as $10, based on filters.

See below (actually using excel pivot for ease) if I select Facebook I only want to see Facebook CPC of £16 not CPC =14 and CPR = 10 as below, how should i configure this in Power BI ?

Filters:

Platform	Facebook
Line of Business	(All)
Product	(All)
CPC	14
CPR	10

Many thanks

[v-diye-msft · Accepted solution]

Hi @Anonymous 

Add the measure:

Measure = var nofiler = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Platform],'Table'[KPI]))
var filc = CALCULATE(COUNTROWS('Table'))
return
IF(nofiler = filc,MAX('Table'[Value]),SUM('Table'[Value]))

Pbix attached. 

[JosefPrakljacic]

Hey @Anonymous,

how about transforming your blanks into a value like "all" so you don't have to do a complex dax calculation?

otherwise - does this code works for you?

Measure =
CALCULATE (
    MIN ( 'Table'[Value] ),
    FILTER (
        'Table',
        'Table'[Line of Business]
            = IF (
                NOT ( ISFILTERED ( 'Table'[Line of Business] ) ),
                BLANK (),
                'Table'[Line of Business]
            )
            && 'Table'[Product]
                = IF ( NOT ( ISFILTERED ( 'Table'[Product] ) ), BLANK (), 'Table'[Product] )
    )
)

[Anonymous]

Also by "how about transforming your blanks into a value like "all" so you don't have to do a complex dax calculation?"

Do you mean in the table simply enter "all" in place of blanks and that will work? (or could work if i try it)?

[JosefPrakljacic]

And regarding your second question.

I would say that you replace your blanks in the Power Query editor to "all" and that it would also work.

May I ask you to check that out and if you encounter some obstacles just write it in that thread.

[Anonymous]

Thanks,

For a multicard the values only appear if a product is selected

I think the issue is that the default for the filters is that everything is blank

All blank means everything is included.

Can we switch the default view of the filters so that all of the entries in a filter are always ticked

Alternatively look at another solution, eg if the count of selections in a filter is >= then for each filter? @JosefPrakljacic 

[Anonymous]

Thanks!

I can't test this now but will do tomorrow first thing.

I've simplied this, do you think I could use that in a switch formula?

[JosefPrakljacic]

I would say yes you can