Forum Discussion

Db111's avatar
Db111
Frequent Visitor
1 year ago
Solved

Problem with filter context

I have created a test scenario (pbix attached) where you choose a salesperson from a slicer and then see their sales, what should be a decreasing RemainingSalesTarget (in my case it creates new rows and doesn't respect the slicer selection but I had something similar working in another report). 

 

I've also created a disconnected table matrix which is meant to show a status summary for the salesperson (trying to do the sort of thing that was easy in paginated reports and using the visual to help with the formatting). But if I click on the various visuals the values change e.g. for Sally RemainingSalesTarget is 9k if I select the current status summary visual and 12k if I select the sales visual.

So issues:

a) why isn't the running sum working in this case

b) why isn't AllSelected in the RemainingSalesTarget respecting the slicer selection

c) how to stop the values changing between visuals

Many thanks for any insights

 

Test scenario.pbix

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Thanks for the reply from audreygerred , please allow me to provide another insight:

    Hi, Db111 
     

    1.Thank you for visiting the Microsoft Fabric Community Forum. We are pleased to hear that changing the filter direction has resolved many issues for you. When you set the relationship to single-direction filtering, it means that other tables cannot filter the SalesTargets table. Therefore, all rows from other tables will display corresponding output values, rather than only the matching rows.

    As shown in the image below:

    Thus, your solution is very effective.

     

    2.Additionally, the reason you see repeated values when you change the InitialTarget value to 18,000 (a fixed value) is that the LOOKUPVALUE function typically returns blank when it does not find a corresponding value. Blank values are normally aggregated in visual objects, which is why you see the duplicates disappear. However, when you output a fixed value, the parts that should output blank and aggregate will now display the fixed value.

    Below is the relevant documentation:

    Work with aggregates (sum, average, and so on) in Power BI - Power BI | Microsoft Learn

     

    3.If you wish to change to a fixed value, you can modify the measure as follows:

    RemainingSalesTarget = 
VAR InitialTarget =lOOKUPVALUE('SalesTargets'[Target], 'SalesTargets'[SalesPerson], 'SalesTargets'[SelectedSalesPerson])
VAR RunningTotalSales = 
    CALCULATE(
        SUM(Sales[SalesAmount]),
        FILTER(
            ALLSELECTED(Sales),
            Sales[Date] <= MAX(Sales[Date])
        )
    )
RETURN IF(NOT(ISBLANK(MAX('Sales'[SalesAmount]))),InitialTarget - RunningTotalSales,BLANK())

    Here's my final result, which I hope meets your requirements.

     

     

    Please find the attached pbix relevant to the case.


    Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
     

    Best Regards,

    Leroy Lu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

    • Db111's avatar
      Db111
      Frequent Visitor
      1 year ago

      Thanks, that fixed issue C. 

      If I change the many to one relationship betwen sales and sales targets cross filter direction from single to both, the duplicate rows are removed. But if I change the VAR InitialTarget = 18000 in the method below the duplicates reappear.
      Any other thoughts please.

      RemainingSalesTarget =
      VAR InitialTarget = LOOKUPVALUE('SalesTargets'[Target], 'SalesTargets'[SalesPerson], 'SalesTargets'[SelectedSalesPerson])
      VAR RunningTotalSales =
          CALCULATE(
              SUM(Sales[SalesAmount]),
              FILTER(
                  ALLSELECTED(Sales),
                  Sales[Date] <= MAX(Sales[Date])
              )
          )
      RETURN
          InitialTarget - RunningTotalSales
      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Thanks for the reply from audreygerred , please allow me to provide another insight:

        Hi, Db111 
         

        1.Thank you for visiting the Microsoft Fabric Community Forum. We are pleased to hear that changing the filter direction has resolved many issues for you. When you set the relationship to single-direction filtering, it means that other tables cannot filter the SalesTargets table. Therefore, all rows from other tables will display corresponding output values, rather than only the matching rows.

        As shown in the image below:

        Thus, your solution is very effective.

         

        2.Additionally, the reason you see repeated values when you change the InitialTarget value to 18,000 (a fixed value) is that the LOOKUPVALUE function typically returns blank when it does not find a corresponding value. Blank values are normally aggregated in visual objects, which is why you see the duplicates disappear. However, when you output a fixed value, the parts that should output blank and aggregate will now display the fixed value.

        Below is the relevant documentation:

        Work with aggregates (sum, average, and so on) in Power BI - Power BI | Microsoft Learn

         

        3.If you wish to change to a fixed value, you can modify the measure as follows:

        RemainingSalesTarget = 
VAR InitialTarget =lOOKUPVALUE('SalesTargets'[Target], 'SalesTargets'[SalesPerson], 'SalesTargets'[SelectedSalesPerson])
VAR RunningTotalSales = 
    CALCULATE(
        SUM(Sales[SalesAmount]),
        FILTER(
            ALLSELECTED(Sales),
            Sales[Date] <= MAX(Sales[Date])
        )
    )
RETURN IF(NOT(ISBLANK(MAX('Sales'[SalesAmount]))),InitialTarget - RunningTotalSales,BLANK())

        Here's my final result, which I hope meets your requirements.

         

         

        Please find the attached pbix relevant to the case.


        Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
         

        Best Regards,

        Leroy Lu

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.