Display unfiltered values from a column when a filter is applied on another column

[datagnan]

Hey there. I'm in need of a little help, much appreciated.

I have attached a pbix file with a sample dataset of my problem.

Basically I have two categorical columns in my dataset (Name and City) and I use the unique values in them as filters. So far so good. What I want to do is when I filter by one of the columns, to be able to see which values are missing in the other column. For example, when I filter the Name column and select, let's say, 'James', I want to display that James doesn't operate in Tokyo.

I have used this topic https://community.powerbi.com/t5/Desktop/Using-EXCEPT-with-ALLEXCEPT-and-ALLSELECTED/m-p/581386 to create a measure that allows me to display the unfiltered values but only from the same column which I am filtering. The other issue with it is that it only works if I concatenate the values, while I'd rather have them in a table.

Essentially I am looking to create a measure that reverses the effect of the filter and displays the unfiltered values from the second column instead of the filtered ones.

Is this possible?

Below is my sample dataset. 

https://drive.google.com/file/d/1002OfyczIpSnVpxNWNHZk1VPcWK64oCw/view?usp=sharing

Thanks a bunch!

[v-yanjiang-msft · Accepted solution]

Hi @datagnan ,

According to your description, I download your sample, here's my solution.

Create a measure.

Missing2 =
VAR _T =
    SELECTCOLUMNS (
        FILTER ( ALL ( 'my_data' ), 'my_data'[Name] = SELECTEDVALUE ( my_data[Name] ) ),
        "City", [City]
    )
RETURN
    CONCATENATEX ( EXCEPT ( ALL ( 'my_data'[City] ), _T ), [City], "," )

Get the expected result.

I attach the sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[datagnan · Accepted solution]

@v-yanjiang-msft  Thanks a bunch, yes your solution totally works for concatenating the unfiltered values. 

I was looking to display them in a table, so I also figured out a solution for that as below:

Created a table with unique values for each 'City' and created a normal relationship (1 to *) with the fact table (my_data), then added 'City' to a table visual.

AllNames = VALUES(my_data[City])

---

Created 3 measures in 'my_data':

filteredCityCount = CALCULATE(COUNTROWS(DISTINCT(my_data[City])))

---

totalCities = CALCULATE(DISTINCTCOUNT(my_data[City]),ALL(my_data))

---

---

finalFilter =

IF ( [totalCities] - [filteredCityCount] = [totalCities], 1, 0)

---

Added finalFilter = 1 as a filter to the table visual. I've updated the sample pbix file in the opening post with both solutions.

[v-yanjiang-msft · Accepted solution]

Hi @datagnan ,

According to your description, I download your sample, here's my solution.

Create a measure.

Missing2 =
VAR _T =
    SELECTCOLUMNS (
        FILTER ( ALL ( 'my_data' ), 'my_data'[Name] = SELECTEDVALUE ( my_data[Name] ) ),
        "City", [City]
    )
RETURN
    CONCATENATEX ( EXCEPT ( ALL ( 'my_data'[City] ), _T ), [City], "," )

Get the expected result.

I attach the sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[CNENFRNL]

Did you ever hear what ADIDAS says? Impossible is nothing.

Spend more time writing mockup dataset and expected result if you want to learning something or even to get a free solution to your question.

[datagnan]

I have uploaded the pbix file with the data and the measure I have worked on, as outlined in the best practices. What is your problem exactly?