Compare text value between row fields in matrix

DAX DAX Calculations
1

Hi All,

I have a matrix in PBI:

Capture

Now I want to create a DAX measure to filter which employees that have the department name inside the [xxx] in their names that are not equal to the Dept Code. For example in this case Peter Nick [FIN] has FIN in his name, which is not equal to the IT department code. The business case here is to identify who has charged their travel costs to the wrong department.

So after creating the DAX measure, I will use the measure to filter on this visual and then the visual will show only Peter Nick as the exceptional case.

I know we can create a new column in Query Editor to compare the [xxx] with the department code but wonder if any way to do it with DAX. I attached the pbix file below.

Travel expense.pbix (42.6 KB)

Thanks so much!

Eric

2

Hi @ericdao,

Welcome to the forum!
Thanks for providing a sample file :+1:
.

Sure you can achieve this with DAX but the proper place for this type work will remain Power Query.
Just create a Calculated column:

Assignment check = 
IF( CONTAINSSTRING( 'Travel Expense'[Employee Name], 'Travel Expense'[Dept] ),
    TRUE(),
    FALSE()
)

.
and a measure, something like:

Wrongly assigned = 
IF( ISINSCOPE( 'Travel Expense'[Employee Name] ) && 
        SELECTEDVALUE( 'Travel Expense'[Assignment check]) = TRUE(),
    BLANK(),
    [Expenses]
)

.
You’ll end up with this:
image

Few other general recommendations:

  • make a habit of creating measures, don’t drag and drop numerical fields on to your matrix/table.
  • if you have any kind of Date value, create a Date dimension table and add that to your model.
  • there is a lot of content here on the forum, so before you ask a question - try the Search option first, you’ll find that in the upper right corner of your screen. There’s a good chance your question has already been asked and answered before.

Here is your file. Travel expense.pbix (52.2 KB)
I hope this is helpful.

1 Like
3

Hi @ericdao, A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

4

It works perfectly. Thank you so much Melissa for your solution as well as the recommendations. Apologies for my delay response due to the holiday.

5

Glad to hear that works well for you. :+1: