topic Re: Filter rows in a table with conditions to a group of values in DAX Commands and Tips

Filter rows in a table with conditions to a group of values

Anonymous — Tue, 02 May 2023 09:28:38 GMT

Hi Community,

I have a table called Concurrent Documents.

As you might have seen from above screen, every Drawing Number divided into Master and Copy(bold and grey) and inside one Master Drawing Numbers there can be several copies.

If Document Category in {Master Document, Project Document} it is Master, If Document Category in {Project Working Copy, Working Copy} it is Copy

The task is that I need to display on table visual Master Drawing Numbers with their copies, however ALL Copy numbers ,must be in Reason for Issue = Z - As Built.

And no matter what status have the Master Document.

For example, in above screen first Master Document 010-0700-JJJ-LAY-20001-03 have copies with Reason For Issue not Z - As Built and we don't need this rows. All copies need to be in Z - As Built. Just like this example:

Hope I explained correctly,

Thank you for your help!

Re: Filter rows in a table with conditions to a group of values

tamerj1 — Tue, 02 May 2023 10:03:57 GMT

Hi Anonymous

place the following message in the filter pane of the matrix visual, select "is not blank" then apply the filter

FilterMeasure =
COUNTROWS (
FILTER (
'Concurrent Documents',
'Concurrent Documents'[Document Category]
IN { "Master Document", "Project Document" }
|| 'Concurrent Documents'[Reason] = "Z - As Built"
)
)

Re: Filter rows in a table with conditions to a group of values

Anonymous — Tue, 02 May 2023 10:42:11 GMT

Hi tamerj1 ,

In this case i get the Master Documents that appears without copies.

In first table I placed the filter, and second as it is.

How should I exclude this Master Documents that occurs alone, I need to display values only with existing Copies that equal to 'Concurrent Documents'[Reason] = "Z - As Built".

Re: Filter rows in a table with conditions to a group of values

tamerj1 — Tue, 02 May 2023 11:44:16 GMT

Anonymous

Would you please paste a screenshot of the dax code that you have used?

Re: Filter rows in a table with conditions to a group of values

Anonymous — Tue, 02 May 2023 12:06:47 GMT

tamerj1
Here the screen, could you check please

Re: Filter rows in a table with conditions to a group of values

tamerj1 — Tue, 02 May 2023 12:05:15 GMT

Anonymous

Please try

FilterMeasure =
COUNTROWS (
FILTER (
'Concurrent Documents',
'Concurrent Documents'[Reason] = "Z - As Built"
)
)

Re: Filter rows in a table with conditions to a group of values

Anonymous — Tue, 02 May 2023 12:15:47 GMT

tamerj1

Now this do the same but ignoring Master Document that have other Reason for Issue Status.

I need to display Masters with their copies which have Reason = Z and if Master Document even have one Copy in another Reason then we need to exlcude all rows with Master Document also🙂