Context issue when adding measure to a table

[Jorritster]

Hi all,

I am creating a model in which I check whether our customers have reached a particular Threshold in a particular country. If this happens we have to report for them more extensively. Therefore, I created the following measure:

Threshold_Passed =

IF([YTD_TaxableBasis] > 0

, IF([YTD_TaxableBasis] > SUM('Thresholds'[Threshold]), TRUE, FALSE)

, BLANK()

)

However, when I add this measure to a table it does not give me the expected output. In the first table you see that adding the measure makes the table retrieve a row for each combination of Direction, Year, Month, Customer, Entity and Country. In the second table you see that without the added Threshold_Passed measure just the country that is in scope is retrieved (in this case the Netherlands). For the other countries there is no data in the database for this customer, so I do not want to see the additional rows which are empty anyways. Besides that, the measure also does not understand my nested if statement (it returns false when the YTD_TaxableBasis is empty). I have checked my relationships and for me they all make sense. Also, the filter context understands that for Tesla only the Netherlands is in scope.

Expected output:

I want to see the second table with an additional column called Threshold_Passed where it gives back False for the first three rows and from April onwards returns True (as YTD_TaxableBasis is higher than the Threshold). 

Complete sample including relationships:

https://www.dropbox.com/s/cw681n31pytcngj/Dummy_Threshold_Management%20V2.pbix?dl=0

Any help or feedback is highly appreciated, as I am really stuck on this. If you need any further information, please let me know.

Kind Regards, Jorrit

[tamerj1]

Hi @Jorritster 
Here is the sample file with the solution https://we.tl/t-Uz6k0eE0dC

[Jorritster]

Hi @tamerj1 ,

That is exactly what I wanted. I will accept it as a solution. However, I have one additional question. As we now change the two-directional filter between contract and country to one-directional, we lose the filter context. 

In my own model when I select a particular entity, I still get all the EU-countries to choose from in the country filter. Although, I preferably only see the countries that are contracted for that entity. 

Is that still possible or should I accept this little drawback?

[v-yiruan-msft]

Hi @Jorritster ,

Please try to set the option Cross filter join as Both just like in below screenshot, later check if the visual can display your expected result...

Best Regards

[Jorritster]

Hi @v-yiruan-msft,

Thanks for your help. I did try this but it creates ambiguity for the Threshold_Passed measure @tamerj1 specified above because of the cross filter:

Is there a solution for this?

[tamerj1]

@Jorritster 

It is working just fine in the sample file I've shared on my previous reply. Please make sure to adjust the relationships as advised. 

[Jorritster]

@tamerj1 Actually it does not:

As you can see it displays multiple countries, while in the contract table only the NL is in scope. So ideally I would love to see just NL in this country slicer in this scenario. Is this possible?

[v-yiruan-msft · Accepted solution]

Hi @Jorritster ,

I updated the sample pbix file provided by @tamerj1 (see attachment), please check if that is what you want...

1. Change the cross filter direction as Both

2. Update the formula of measure [Threshold_Passed] as below

Threshold_Passed =

VAR YTDTaxable =

[YTD_TaxableBasis]

VAR Threshold =

CALCULATE (

SUM ( 'Thresholds'[Threshold] ),

FILTER('CDM','CDM'[Direction]=SELECTEDVALUE('Direction'[Direction]))   // CROSSFILTER ( CDM[Direction], Direction[Direction], BOTH )

)

VAR Result =

IF (

YTDTaxable > Threshold,

"TRUE",

IF (

NOT ISBLANK ( Threshold ),

"FALSE"

)

)

RETURN

Result

Best Regards