IF statement use same criteria in Filterslicer & in Column Visual & in IF statement

[Anonymous]

Hello PBI community,

I have an issue with the use of the IF statement. (Well not really the IF since its more a visual problem. See print screen attach:
1- Is the amount in CHF that should appear in 2
2- Is this formula // Amount MC AC Rates = IF([Market selected] = “ESL Group” ; [CHF AC GC Argo CC Integra Argo AY Rates] ;[MC AC CCamount conversion Argo AY Rates]) //
3- The Market Criteria in the visual.

Screen Shot 2019-01-16 at 11.24.28.jpg1048x251 87.2 KB

I m using this formula so that if no market is selected we get “ESL Group” which should lead then to use the [CHF AC GC Argo CC Integra Argo AY Rates] from the previous IF measure.

Market Selected = SELECTEDVALUE(‘Cost Center’[Market] ; “ESL Group”)

But, since the Market is in the SELECTEDVALUE measure and in the Visual, I always get a false to my IF function.

I tried to remove the filter with the All measure but then it doesn’t split by market in my visual (Same amount for all markets)

The point here is really to get the data of the Group in CHF by market, but then be able to filter a market and get the amount in SEK for Sweden (for exmemple)

Any ideas

Regards

Mark Lovejoy

[AlB]

Hi @Anonymous

I don't understand what you mean by the following. Can you explain it a bit more?

But, since the Market is in the SELECTEDVALUE measure and in the Visual, I always get a false to my IF function.

I tried to remove the filter with the All measure but then it doesn’t split by market in my visual (Same amount for all markets)

[Anonymous]

Hello @AlB

The visual has markets (Sweden, Swizerland, France,...) in lignes so it is splitting everything by markets. And this criteria in the visual is taking the lead over the IF function. Which means that my function return always false since it is not considering the "ESL Group" created in the Selectedvalue measure. (I have a filter slicer with the market and when I dont select anything from this slicer I get ESL Group) And even tho the slicer is giving me ESL Group in the IF measure, I get result is false because the Market crietria is in the Visual.

Regards

Mark

[AlB]

@Anonymous

Is ‘Cost Center’[Market]  what you have in the rows of your matrix visual as well?

Try this:

Market Selected = SELECTEDVALUE(ALLSELECTED(‘Cost Center’[Market]) ; “ESL Group”)

[Anonymous]

Yes Market is what I have in Rows of my visual.

[AlB · Accepted solution]

@Anonymous

Try this then:

Market Selected =
CALCULATE (
    SELECTEDVALUE ( 'Cost Center'[Market], "ESL Group" ),
    ALLSELECTED ( 'Cost Center'[Market] )
)

It will yield the same result when nothing is selected in the slicer and when everything is selected. If you need to tell those two cases apart you can use:

SlicerTest=
CALCULATE (
    ISFILTERED ( 'Cost Center'[Market] ),
    ALLSELECTED ( 'Cost Center'[Market] )
)

which will yield FALSE if nothing is selected in the slicer, TRUE in all other cases

Code formatted with  

[Anonymous]

@AlB

Market Selected =
CALCULATE (
    SELECTEDVALUE ( 'Cost Center'[Market], "ESL Group" ),
    ALLSELECTED ( 'Cost Center'[Market] )
)

Is the soltuon it works perfectly. Thanks a lot mate.

Regards

Mark Lovejoy

[Anonymous]

SELECTEDVALUE  requires a Columnname as first argument

[Anonymous]

@AlB