Different usage of filter in measure and display of 0 values

[PBI_alma]

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!

[Anonymous]

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.

[WinterMist]

@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

[PBI_alma]

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

[WinterMist]

@PBI_alma 

Still working on this.

Nathan

[PBI_alma]

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

[WinterMist]

@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

[PBI_alma]

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

[WinterMist]

@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

[WinterMist]

@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

[PBI_alma]

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

[PBI_alma]

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

[WinterMist]

@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