Show results on report when a row meets certain criteria

[TK16]

I am looking for how to show results on a report only if one of the items is true. For ex:

CustName  Cust#   Form

John Smith  12345   Form A

Jane Doe      67890  Form B

Bugs bunny  99999  Form A & FormB

I only want to see those that have ONLY Form  A

[TK16]

I wish it was as simple as that.  Form A is a request for Services. Form B is that the request has been completed.  I want to see only those customers that have a request for services which have not been completed, so where there is only the existence of Form A not A & B.  Hope this helps clarify

[v-jingzhang · Accepted solution]

Hi @TK16 

You can first use Power Query Editor to transform the table. Steps are:

1. Split the Form column into Rows by delimiter "&";

2. Trim the new Form column to remove addtional leading or tailing spaces.

You will get table in the following format. Apply the change to the report. 

In report, create the following measure and use it as a visual-level filter on the table visual. Set it to show items when value is 1. 

Filter measure = 
VAR _form = CALCULATE(SELECTEDVALUE('Table'[Form]),ALLEXCEPT('Table','Table'[CustName],'Table'[Cust#]))
RETURN
IF(_form = "Form A", 1, 0)

If you don't want to change the table format, you can try the following measure. 

Filter Measure 2 = IF(CONTAINSSTRING(SELECTEDVALUE('Table (2)'[Form]),"Form A") && NOT CONTAINSSTRING(SELECTEDVALUE('Table (2)'[Form]),"&"), 1, 0)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Syk]

Add you "Form" field to the filter pane and then select "Form A" in the basic filtering. 

Similar to how I'm showing only Austin data in this screenshot example