Filtering data based on 2 columns in dataset

[jasonyeung87]

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
 

[v-kelly-msft]

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:

https://radacad.com/live-connection-when-power-bi-comes-hybrid#:~:text=Modeling%20and%20Report%20Lev...

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[jasonyeung87]

Hi Kelly,

The New measure button is greyed out for me. Is there a way to activate this button?

Sincerely,

Jason

[Anonymous]

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

[jasonyeung87]

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

[Anonymous]

You should have no issues creating a report level meaure with a live connection 

[GilbertQ]

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?