Forum Discussion

Jeff_v's avatar
Jeff_v
New Member
2 years ago

IF statement on a countrows from a relatedtable breaks the filtering

Hi,
I have a Table A filtered with one external filter (slicer).
This Table A has a one to many relationship with a Table B.
The Table B has 2 external filters (slicers).

I have created a measure in Table A that returns
COUNTROWS(RELATEDTABLE(Table B))

and this measure works fine.
As soon I try to use this measure in another measure, then the filtering on the Table A is broken.

for instance this measure breaks the filtering:
IF(ISBLANK(Measure1),0,1)
I tried to embedded it into a CALCULATE,

CALCULATE(
         IF(ISBLANK(Measure1),0,1),
         TableA[Key] = 'item26'
 )

It is still not working, the column "Key" is not filtered.

Would you have any clue for me pls ?

Need Help
Show and Tell

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    Jeff_v , As they are joined simply use

     

    COUNTROWS(Table B)

    then try

    IF(ISBLANK([Measure1]),0,1)

     

     

    CALCULATE(
    IF(ISBLANK([Measure1]),0,1),
    TableA[Key] = 'item26'
    )

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Jeff_v ,

    Firstly, it's important to understand that the 'IF' function checks a condition and returns one value if TRUE, and another if FALSE. When you're using 'COUNTROWS(RELATEDTABLE(Table B))' in your measure and it works fine, it indicates that the relationship and row context are correctly established. However, when you incorporate this measure into another measure using 'IF(ISBLANK(Measure1),0,1)', and observe that the filtering breaks, it suggests that the issue might lie in how the filter context is being applied or interpreted.

     

    The 'CALCULATE' function could modify the filter context of a calculation, but it requires careful handling to ensure it applies the context as intended. When you tried embedding your measure in a `CALCULATE` function with a specific filter, and it didn't work as expected, it might be due to how the filter context is being passed or overridden.

     

    Here are a few steps and considerations to troubleshoot and potentially resolve the issue:

    1. Ensure Correct Filter Context: Make sure that the filter context from your slicers is correctly applied to Table A and Table B. The 'CALCULATE' function can modify the existing filter context, so it's crucial to ensure it's not unintentionally overriding the slicer filters.

     

    2. Use of 'ALL' Function: If you're trying to ignore certain filters with `CALCULATE`, consider using the 'ALL' function to remove filters from specific columns or tables. For example, 'CALCULATE([Measure], ALL(TableA[Key]))' would calculate the measure while ignoring filters on the 'Key' column of Table A. However, use this with caution as it changes the filter context.

     

    3. Review Relationship Behavior: Double-check the relationships between Table A and Table B. Ensure that they are set up to correctly propagate filters as expected. Incorrect or inactive relationships can lead to unexpected filtering behavior.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.