Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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
Solved! Go to 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
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)
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
@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”)
Yes Market is what I have in Rows of my visual.
@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
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
SELECTEDVALUE requires a Columnname as first argument