Forum Discussion

ChrisR22's avatar
ChrisR22
Icon for Helper III rankHelper III
1 year ago
Solved

Measure not abiding by filters

Hello,

 

I have a measure that compares the values of two columns from different tables, and returns whether the values match or not.

PCAP Amount Match =
var _Table1 = SUM(Table1[Amount])
var _Table2 = SUM(Table2[PCAP Amount])
return
IF(ROUND(_Table1, 2) = ROUND(_Table2,2), "Match", "Break")
 
I have them in a table that is based on a shared field (Transaction Name). Additionally, there is a separate field (Field A) that is present in only one of the tables, that I am using to limit the scope of the visual table. 
 
My problem is, this filter stops applying to the table when I add the measure to the visual. How do I update the measure so that it abides by the filter I am applying to the visual?
 
Screenshots:
This is the table prior to the measure being added. These are the correct rows that are supposed to appear.

 

and this is what I receive when I add in the measure:

 the rows in the red box should not appear, because they are being filted out by the filter I have on Field A. 

 

How do I ensure that the measure takes this into account (short of just filtering out for blank records, which could lead to lost data).

 

Thank you in advance!


 

Need Help
Show and Tell
  • ArtisanAmbrosia's avatar
    ArtisanAmbrosia
    1 year ago

    Gotcha, I missed that part! 
    I think the KEEPFILTERS function should do the trick. Something like:

    PCAP Amount Match =
    VAR _Table1 = CALCULATE(SUM(Table1[Amount]), KEEPFILTERS(Table1[FieldA]))
    VAR _Table2 = CALCULATE(SUM(Table2[PCAP Amount]), KEEPFILTERS(Table2[FieldA]))
    return
    IF(ROUND(_Table1, 2) = ROUND(_Table2,2), "Match", "Break")

6 Replies

  • ArtisanAmbrosia's avatar
    ArtisanAmbrosia
    Frequent Visitor
    1 year ago

    I think adding a function to consider whether the Amount fields are null would do the trick. The below wouldn't produce a result if either _Table1 and _Table2 aren't null. Replace the || with && if you want to only produce a result where both _Table1 and _Table2 have a value.

     

    PCAP Amount Match =
    VAR _Table1 = SUM(Table1[Amount])
    VAR _Table2 = SUM(Table2[PCAP Amount])
    RETURN
    IF(
    ISBLANK(_Table1) || ISBLANK(_Table2),
    BLANK(),
    IF(ROUND(_Table1, 2) = ROUND(_Table2, 2), "Match", BLANK())
    )

    • ChrisR22's avatar
      ChrisR22
      Icon for Helper III rankHelper III
      1 year ago

      ArtisanAmbrosia thanks for the reply! The problem is, some of the all blanks further down in the table I want to keep, so it's not as simple as just filtering out the blanks (as stated above). I need it to review the filter and act accordingly (if possible). Is there somewhere I can incorporate that filter into the measure? It doesn't sees so because the filter reviews each row, there is no aggregation expression in it.

      • ArtisanAmbrosia's avatar
        ArtisanAmbrosia
        Frequent Visitor
        1 year ago

        Gotcha, I missed that part! 
        I think the KEEPFILTERS function should do the trick. Something like:

        PCAP Amount Match =
        VAR _Table1 = CALCULATE(SUM(Table1[Amount]), KEEPFILTERS(Table1[FieldA]))
        VAR _Table2 = CALCULATE(SUM(Table2[PCAP Amount]), KEEPFILTERS(Table2[FieldA]))
        return
        IF(ROUND(_Table1, 2) = ROUND(_Table2,2), "Match", "Break")
  • ChrisR22's avatar
    ChrisR22
    Icon for Helper III rankHelper III
    1 year ago

    scratch that, my bad, I still had the filter on for the blank values. The KeepFilters piece didn't seem to do anything to the table.

  • ChrisR22's avatar
    ChrisR22
    Icon for Helper III rankHelper III
    1 year ago

    the problem with your solution is that the filter field is only in table 2, its not in table 1. Does that provide you with any insight?