Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JonSwed
Advocate II
Advocate II

Case sensitive text filter

Hi all.

Working with Salesforce id data which, as some of you may know, distinguishes between users via case. For example, 001J000001Hs5Ss and 001J000001Hs5SS are two different users. This is the 15 character id, which is turned into 18 characters to get around the case issue.

However - I need to give users of my dashboard the option to search by the 15 character id.
I have created a new column which removes the case insensitivity issue from the data and returns the ids correctly.
I used this method to solve the insenstivity issue:
https://blog.crossjoin.co.uk/2019/10/06/power-bi-and-case-sensitivity/ 

But, I cannot get the text filter box to find them.

For example, powerbi sees 001D000001Ys5SS and 001D000001Ys5SS  only as 001D000001Ys5SS.

My calculated column returns, correctly, both 001D000001Ys5SS and 001D000001Ys5Ss.

The search box only finds 001D000001Ys5SS.

I have set my search box to search the calculated column, but it does not seem to be able to respond to the different cases.

Any suggestions gratefully appreciated.











6 REPLIES 6
JonSwed
Advocate II
Advocate II

Thanks for that - unfortunately in this use case the 18 digit id is not an option.

I'll explore using DAX, though I wonder whether this will give me the option of  a dashboard user searching via a ID they input. 

If you want I can show you a Power Query function that calculates the case safe id.

 

For the DAX option you will need a custom visual like TextFilter to accept user input.

That sounds great  - seeing the Power Query function would be very useful.

Not sure how the DAX option would work, could you share more detail?

Many thanks!

Here's the Power Query function. Looks a little ugly but works well at scale.

 

 

(ObjectID as text) as text => 
let
    AN = "ABCDEFGHIJKLMNOPQRSTUVWXYZ",
    NFTC = (pos) => Number.From(Text.Contains(AN, Text.At(ObjectID,pos))),
    FirstIndex = NFTC(0) + NFTC(1)*2 + NFTC(2)*4 + NFTC(3)*8 + NFTC(4)*16,
    SecondIndex = NFTC(5) + NFTC(6)*2 + NFTC(7)*4 + NFTC(8)*8 + NFTC(9)*16,
    ThirdIndex = NFTC(10) + NFTC(11)*2 + NFTC(12)*4 + NFTC(13)*8 + NFTC(14)*16,
    ANP = AN & "012345"
in
    ObjectID & Text.At(ANP, FirstIndex) & Text.At(ANP, SecondIndex) & Text.At(ANP, ThirdIndex)

 

Nothing much to share yet on the DAX option - still trying to work around the fact that DAX does not support global variables or parameters. We should raise an idea to allow text values for What-if  parameters.

 

lbendlin_0-1637855941008.png

 

 

I didn't know Power Query can do bit shifts.  This "simplifies"  the query a little. Ahem..

 

 

(ObjectID as text) as text => 
let
    AN = "ABCDEFGHIJKLMNOPQRSTUVWXYZ",
    NFTC = (pos) => Number.From(Text.Contains(AN, Text.At(ObjectID,pos))),
    SM = (offset) => List.Accumulate({1..4},NFTC(offset), (s,c)=> s + Number.BitwiseShiftLeft(NFTC(c+offset),c))
in
    ObjectID & Text.At(AN & "012345", SM(0)) & Text.At(AN & "012345", SM(5)) & Text.At(AN & "012345", SM(10))

 

lbendlin
Super User
Super User

It is what it is. Power BI by default is not case sensitive.  Use the Case Safe ID (the 18 digit version) instead.

 

Or create a klutz based on the FIND() DAX function - that one is actually case sensitive.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.