topic Re: Different usage of filter in measure and display of 0 values in DAX Commands and Tips

Different usage of filter in measure and display of 0 values

PBI_alma — Wed, 29 Nov 2023 17:00:00 GMT

Hello everyone,

I have a topic here that I'm stuck on:

I have 2 filters in the report and a table in which the corresponding values are to be displayed.

Filter 2 is the must-have value: all values in the table should be restricted accordingly. Filter 1 should only be taken into account for a measure with a distinct count; if the filter does not match, 0 should be displayed. To achieve this, I have separated filter 1 from the table with the interaction. But what must the measure be so that the value from filter 1 is still taken into account for the distinct count?

Thank you very much for your tips!

Re: Different usage of filter in measure and display of 0 values

WinterMist — Wed, 29 Nov 2023 17:18:06 GMT

PBI_alma

Would it be possible to share a link to the PBIX or provide a small mock-up example of data, filters & expected result? (Mock-up can be done with screenshots from Excel)

I'm struggling to follow the abstract description.

Also not sure what you mean by "filter 2 is the must-have value".

Regards,

Nathan

Re: Different usage of filter in measure and display of 0 values

PBI_alma — Wed, 29 Nov 2023 17:36:02 GMT

Hi WinterMist

thank you very much for your response.

Here are examples for the two filters:

If I filter for a city in filter 2 and for a user in filter 1: I want to see only all products in the table that match the city, that is my must-have value. Then I want to count in the table how many products (and other different attributes) of a category the filtered user has bought (how often the user appears in different rows). If the user has not bought any, this should still be displayed as 0.

I therefore tried to separate the table from filter 1 in the interactions so that I not only see the products of the filtered user, but all products in the respective city.

Hope that was understandable? thank you very much and best regards

Re: Different usage of filter in measure and display of 0 values

WinterMist — Wed, 29 Nov 2023 20:40:16 GMT

PBI_alma

Thanks for your example.

However, I still need more help in understanding.

So I created the following model & visual example.

You wrote:

"If I filter for a city in filter 2 and for a user in filter 1:

I want to see only all products in the table that match the city, that is my must-have value."

Does this mean that the table visual should:

- apply the City filter?

- and IGNORE the User filter?

This can be done using "Edit Interactions".

However, doing this will cause non-intuitive behavior.

For example, records for user "John" occur in Miami. But if the user selects:

- City = Chicago

- User = John

THEN the record for Sarah in Chicago appears, because the User slicer has no impact on the table visual.

Is this really what you want?

Perhaps I am still not understanding correctly?

Regards,

Nathan

Re: Different usage of filter in measure and display of 0 values

WinterMist — Wed, 29 Nov 2023 21:02:31 GMT

PBI_alma

You also wrote:

"Then I want to count in the table how many products (and other different attributes) of a category the filtered user has bought (how often the user appears in different rows). If the user has not bought any, this should still be displayed as 0.

To see "how often the user appears in different rows" in the table visual, you can use the following measure.

Not sure if this is what you are looking for?

Regards,

Nathan

Re: Different usage of filter in measure and display of 0 values

PBI_alma — Thu, 30 Nov 2023 06:44:09 GMT

Hi Nathan, thank you very much for your efforts to help me.

Exactly, I want to restrict the view in the table to Chicago in your example. Then I want to see all users (John, Sarah, Ed,...), but only count John's rows, because John is filtered,

In the end, the users should not be displayed in the table, but only the number of times the filtered user applies, depending on the user filtering. So I can filter for 0 times and have a negative filtering. I filter for John and see where John does NOT apply. Does that make sense? That's why the zero display is important to me.
Do you have any other suggestions for a solution? Since not only John's products should be displayed, but also those that he does not have, I had separated the interaction between table and filter.

Many thanks and best regards

Alma

Re: Different usage of filter in measure and display of 0 values

WinterMist — Thu, 30 Nov 2023 20:32:03 GMT

PBI_alma

My apologies.

Can you provide Excel screenshots of the exact output you are expecting to see in the following 3 scenarios?

NOTE: Here is the test data without any slicer values selected.

Scenario 1)

- City = Chicago

- User = John

Scenario 2)

- City = Miami

- User = Ed

Scenario 3)

- City = Los Angeles

- User = Jane

Regards,

Nathan

Re: Different usage of filter in measure and display of 0 values

PBI_alma — Sat, 02 Dec 2023 09:11:01 GMT

Hi WinterMist,

sure:

To make it more clear:

I filter some more sales for Chicago (so I only want to see the results of Chicago) and John (but I want to see the other users as well, but only count John in his rows) and want to get the following result:

thank you,

regards,

Alma

Re: Different usage of filter in measure and display of 0 values

WinterMist — Tue, 05 Dec 2023 23:10:03 GMT

PBI_alma

Still working on this.

Nathan

Re: Different usage of filter in measure and display of 0 values

PBI_alma — Fri, 08 Dec 2023 16:26:49 GMT

Many thanks for your efforts WinterMist , it would be great if you could find a solution! Have a nice weekend, Alma

Re: Different usage of filter in measure and display of 0 values

WinterMist — Tue, 12 Dec 2023 14:48:45 GMT

PBI_alma

I apologize. It appears I'm in over my head.

This is going to require help from someone much smarter than I am.

I thought this would be relatively easy:

1) I've got the current user ID for each row: [02 Current User ID].

2) I've got the list of selected user ID's from the slicer: [01 Selected Users].

3) Step 3 should be easy. All we have to do now is create a measure that checks if #1 exists in #2.

Unfortunately, it's not easy at all (at least for me).

No matter what I've tried, it's been unsuccessful.

Regards,

Nathan

Re: Different usage of filter in measure and display of 0 values

PBI_alma — Tue, 19 Dec 2023 16:31:24 GMT

Hi Nathan, thank you very much for all your time and effort!

Re: Different usage of filter in measure and display of 0 values

Anonymous — Tue, 26 Dec 2023 07:44:20 GMT

Hi PBI_alma ,

Below is my table:

You can create a new table:

Table 2 = SUMMARIZE('Table','Table'[city],'Table'[user])

The following DAX might work for you:

Measure = var _user = MAX('Table'[user]) var _user2 = SELECTEDVALUE('Table 2'[user]) RETURN CALCULATE( COUNTROWS('Table')+0, 'Table'[user] = _user2 )

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.