Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Win a FREE 3 Day Ticket to FabCon Vienna. Apply now

Reply
PCHMarc67
Frequent Visitor

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 each category because I can summarize the donor ID number (which is in the source spreadsheet);  I have summarized this count either by the ID field + count or I have a measure that just counts (so the total is a dynamic value, NOT a static value in my source table)

PCHMarc67_0-1723760670928.png

what I don't understand how to do is to filter (via a Slicer or somehow in Dax?) so that I answer "show me all the donors (rows) that have <>blank in "Digital Philanthropy" AND <> blank in "Direct Response." (as just one example)    And I want to see just those donors that have those two, but then I want to see the distribution for that group with the rest of their appeal category totals.

 

so in my screenshot above, the filter/code should capture the two in red only, but show their other distributions as well (if there were any, but blank is ok to show).  I'm really looking for a slicer that I can click on instead of having to hard-code combinations because I want to explore the most likely combinations of appeal groups.  

 

thx for any direction here. Marc

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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:

  vlinhuizhmsft_0-1724318431999.png

 

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

  vlinhuizhmsft_1-1724318455799.png

 

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.

vlinhuizhmsft_2-1724318487436.png

 

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

 vlinhuizhmsft_5-1724317219966.png

 

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!

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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:

  vlinhuizhmsft_0-1724318431999.png

 

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

  vlinhuizhmsft_1-1724318455799.png

 

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.

vlinhuizhmsft_2-1724318487436.png

 

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

 vlinhuizhmsft_5-1724317219966.png

 

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
Super User
Super User

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

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

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.