How to filter my visual so it affects only subset of the data

[Vesa91]

This is simplified version of my table:

Category

A

A

B

B

C

C

C

So I want to include all the C rows but filter some A and B rows based on if they are found in different table. C rows are never found in other table so I have to somehow exclude them from the filtering. So end table should look something like this :

Category

A

B

C

C

C

How can I achieve this?

[v-tangjie-msft]

Hi @Vesa91 ,

Thanks @PawelWrona   for the quick reply and solution. Here is my alternative approach for your reference:

(1)This is my test data.  

Table1:

Table2:

(2)We can create a table and then the result is as follows.

Table = UNION(EXCEPT('Table1','Table2'),'Table2')

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[PawelWrona]

Hello. You could either merge tables in Power Query, to bring new column to first table informing you whether there is a match between tables or not. You could also load both tables into the model, and create relationship between them based on the common column. When they are in the model, you could use 2nd table from your example to filter the first table. Let me know if more details are needed.

[Vesa91]

Hey and thanks for the response. I have already brought them both (and many other tables to my model) but I don't know how to use other tables' columns as filters in visuals without losing the C rows from the visual.

[PawelWrona]

Ok, I am still not sure how much your model is simplified, but I provided the following:

This should work based on my limited knowledge of your model. I have a Main table like this:

Additional table that looks like this:

There is a following relationship setup:

And here is the code for a measure:

New Measure =

VAR categories = {"C"}

VAR itemCount = COUNT(Main[Item])

VAR itemsInCategories =

    CALCULATE(

        COUNT(Main[Item]),

        REMOVEFILTERS(Additional[Filter]),

        Main[Category] IN Categories

    )

RETURN

IF(

    ISFILTERED(Additional[Filter]),

    itemCount + itemsInCategories,

    itemCount

)

You can put more categories into the variable.

[Vesa91]

Okay thank you, that seems promising but I am just too much of a beginneer to really wrap my head around how to use that measure. I can however try to elaborate my situation:

Main table

Category	ID
A	1
A	2
B	3
B	4
C	5
C	6
C	7

Additional table

ID
1
3
6
22
33
55
66

I need to make a pie chart that has all the C rows from the main table and only the A and B rows that match with the additional table ID. I have already accomplished this by using Power Query where I just take C rows in one query and filter the A and B rows with additional table in an other query and append the two together and load that table in my model and connect that with 1:1 connection to my main table. My visualisation has multiple other filters like date and such that needs the original main table data too so I need both.

My solution however seems little bit forced to me but I'll take it if I can't do it any other way. What I was trying to do is use main table data in pie chart visualisation and do visualisation level filtering so that I don't have to make new table all together. 

[PawelWrona]

So, you case is almost identical as the one that I used. You will setup the relationship as I did, but based on the ID column in both tables. Then, I understand that you will be filtering the data based on the IDs, not some other columns. In this case, DAX Measure will change like this:

New Measure =

VAR categories = {"C"}

VAR itemCount = COUNT(Main[ID])

VAR itemsInCategories =

    CALCULATE(

        COUNT(Main[ID]),

        REMOVEFILTERS(Additional[ID]),

        Main[Category] IN Categories

    )

RETURN

IF(

    ISFILTERED(Additional[ID]),

    itemCount + itemsInCategories,

    itemCount

)

The way this measure works is:

1. It checks if you applied any filter on the ID column in your Additional Table.

2. If there is no filter applied, it returns the simple count of all the items.

3. If there is filter applied, it will return the cound of filtered items + all the items coming from Category C.

[Vesa91]

Hey again and thanks for help and support. This almost seems to work. I am not sure if I am doing it correctly so I try explain: I created this measure and used is as value in my pie chart and then when I filtered out non-matching rows in additional table, values seemed to all have category C values plus their real value. So it returned correct amount for category C (though the label was empty) and for every other category correct amount plus value of that empty category.

[PawelWrona]

If we could see your output vs expected value that would be much easier to discuss. But based on your description I think you are doing it correctly.

[Vesa91 · Accepted solution]

I fixed some parts of the measure and I made it work! Thank you for your help. So what I did was I added one if statement to check if I was handling C rows and removed the Item count because unlike I stated earler, the additional table did have some C matching rows so I don't want to count them twice.

New Measure =

VAR categories = {"C"}

VAR itemCount = COUNT(Main[Item])

VAR itemsInCategories =

    CALCULATE(

        COUNT(Main[Item]),

        REMOVEFILTERS(Additional[Filter]),

        Main[Category] IN Categories

    )

RETURN

IF(

    ISFILTERED(Additional[Filter]),

    IF(

         MAX(Main(Category) = "C",
         itemsInCategories,

         itemCount

    ),

    itemCount

)

Not sure if the Max function is the best option to check the category though

[Vesa91]

Okay I spent some time looking at the measure. I think when the additional table is filtered, it goes to calculate function and counts the number of C-rows even in cases of A and B and by so inflating their value.