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 ...
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.

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.