Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 | Cost per click | 16 | |||
2 | Retail | Cost per click | 14 | ||
3 | Corporate | Cars | Cost per click | 12 | |
4 | 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 | |
Line of Business | (All) |
Product | (All) |
CPC | 14 |
CPR | 10 |
Many thanks
Solved! Go to 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.
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.
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] )
)
)
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)?
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.
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
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |