Forum Discussion

PCHMarc67's avatar
PCHMarc67
Frequent Visitor
2 years ago
Solved

Dynamic(?) Filter to Find Two Values but only if those two values are not blank

Hi,  I have a BI Report Matrix visual that tells me individual donor names by row and Gift appeal category (like a giving channel) for vertical rows.  I can total the number of gifts by donor by eac...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Thanks for the reply from lbendlin.

     

    Hi PCHMarc67 ,

    I tried to create a sample data myself based on the your requirement and implemented the result. Please check if there is anything that can be improved. Here is my solution:

     

    Sample data is as follows:

      

     

    1.Use the VALUES function to get two new categories for each of the two slicer fields.

      

     

    2.Create a measure:

    Measure =
    VAR selectone=MAX('select1'[category])
    VAR s1=CALCULATE(SUM('Table'[amount]),FILTER('Table','Table'[category]=selectone))
    VAR selecttwo=MAX('select2'[category])
    VAR s2=CALCULATE(SUM('Table'[amount]),FILTER('Table','Table'[category]=selecttwo))
    RETURN
    IF(NOT(ISBLANK(s1))&&NOT(ISBLANK(s2)),1,0) ​​

     

    3.Filter the matrix using measure created.

     

    4.The final result is as follows with the case where both are non-empty and contain other kinds of distributions :

     

     

    Best Regards,
    Zhu
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago
 "show me all the donors (rows) that have <>blank in "Digital Philanthropy" AND <> blank in "Direct Response."

at the same time? IE a donor should have data in both categories?

 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523