Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to 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.
@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 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.
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.
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.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |