Forum Discussion

Jorritster's avatar
Jorritster
Frequent Visitor
4 years ago
Solved

Context issue when adding measure to a table

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

  • Anonymous's avatar
    Anonymous
    4 years ago

    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

7 Replies

    • Jorritster's avatar
      Jorritster
      Frequent Visitor
      4 years ago

      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?

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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