Forum Discussion

JonSwed's avatar
JonSwed
Advocate II
4 years ago

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 ...
Need Help
JonSwed's avatar
JonSwed
Advocate II
4 years ago

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. 

lbendlin's avatar
lbendlin
Super User
4 years ago

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.

  • JonSwed's avatar
    JonSwed
    Advocate II
    4 years ago

    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!

    • lbendlin's avatar
      lbendlin
      Super User
      4 years ago

      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's avatar
        lbendlin
        Super User
        4 years ago

        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))