Forum Discussion
Filtering data based on 2 columns in dataset
Hi,
I'm using the table visualization to display data from an analysis services database. The data looks similar to the following:
| Customer Name | User Name |
| Jason | Jay |
| John | John |
| Julian | Jul |
| Jack | |
| Jimmy | Jimmy |
I want a filter so that Power BI would only display records if Customer Name does not match the User Name or the User Name is not blank. The end result would be the following:
| Customer Name | User Name |
| Jason | Jay |
| Julian | Jul |
I was wondering if it's possible to do this via the filters? Or would I have to update the Analysis Services database? I want to avoid doing the latter as much as possible.
Sincerely,
Jason
6 Replies
- Hi there
As far as i can tell you would have to do this in Analysis Services. I am not sure how you would create a filter without creating it in a table? Have you considered using a DAX measure?
how is your data structured? is it in columns in a fact table?
if so then you could add a column with an IF statement as an example
User Names Match = if(customer name = user name , "Match" , "Does not Match" )then add the column to a filter for the matrix and select "Match" and that should do it
Hi Dnerada,
The data is from the Analysis Services database and is not structured with dim / fact tables. We're connecting to it live, so we can't modify the Cubes. I'll see if I could add a measure, but because it is a live connection, it may not be possible.
Sincerely,
Jason
Hi jasonyeung87 ,
You can create a report level measure in live connection mode,such as :
measure=IF(MAX('table'[Customer Name])<>MAX('table'[User Name])&&MAX('table[User Name])<>BLANK(),MAX('table'[Customer Name]),BLANK())Put measure with column [User Name],you will see your expected output.
Below is the reference:
Best Regards,
KellyDid I answer your question? Mark my post as a solution!Hi Kelly,
The New measure button is greyed out for me. Is there a way to activate this button?
Sincerely,
Jason
