topic Re: How to leave out one field when whole matrix is filtered? in DAX Commands and Tips

How to leave out one field when whole matrix is filtered?

Matt78 — Thu, 02 Nov 2023 08:18:27 GMT

Hello

I have a list or orders that contains vendor no's and qty or orders from each. My matrix is filtered that it calculated only purchases that contain items, leaving out purchases that are in G/L accounts. However, we have one vendor which should be in this list even if ALL of purchases are in G/L accounts, not items.

I have filtered my matrix so that it leaves out purchases which have item category = empty

How can I have this one vendor to be visible in my list, so that my filtering wouldn't apply to this certain vendor?

Matt

Re: How to leave out one field when whole matrix is filtered?

lbendlin — Sat, 04 Nov 2023 15:04:17 GMT

probably needs dummy item lines.

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that 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.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: How to leave out one field when whole matrix is filtered?

Matt78 — Mon, 06 Nov 2023 06:59:00 GMT

Vendor	Item Category	Quantity
50772		20000
90001	CAT	7000
80003	DOG	50000

I think this could be solved with Replacer.ReplaceText and Table.ReplaceValue functions but can't seem to get them right. Appreciate the help. End result should be: if vender number is 50772 then Item Category is "X"

Matti

Re: How to leave out one field when whole matrix is filtered?

lbendlin — Mon, 06 Nov 2023 15:55:54 GMT

I thought you wanted to display vendor 50772 regardless of what item category is selected/filtered?

Re: How to leave out one field when whole matrix is filtered?

Matt78 — Mon, 06 Nov 2023 16:28:11 GMT

I want to display vendors which have item category, with the exception if vendor 50772 which doesn't have item category. So I was thinking to use replace function to replace empty field(s) with X.

Re: How to leave out one field when whole matrix is filtered?

lbendlin — Mon, 06 Nov 2023 21:40:00 GMT

Do you plan to use a category slicer? Will your users know to always select "X" on that slicer?

Re: How to leave out one field when whole matrix is filtered?

Matt78 — Tue, 07 Nov 2023 06:34:06 GMT

Yes, this report is based on purchase invoice lines. The whole report page is filtered that only lines with item categories are visible. This is the way to leave out for example phone bills etc and show only purchases of items. The exception is vendor 50772 and it doesn't matter which item category it has, until it is something else as empty. If this vendor has item category "X" and I will choose this, then all purchases will be visible for this vendor