Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Additional wildcard Filter for LOOKUPVALUE

Hello & Happy Friday,

 

Using the DAX LOOKUPVALUE code below is it possible add an additonal wildcard filter on CRITSITS'[CritSit Event Type] = "*Metric*"?

 

The DAX code below works great but I need an additional wildcard filter to narrow down / pin point the desired results.

 

Thank you in advance

 

 

CritSitVerified = 
    IF(ISBLANK(
        LOOKUPVALUE('CRITSITS'[CritSit Event Type],'CRITSITS'[Ticket ID], 'CROutages'[RTPA])),"No","Yes"
)

 

 

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    You may have better luck with just simple filtering like this:

     

    CritSitVerified = 
    IF(ISBLANK(
      MAXX(
        FILTER(
          'CRITSITS',
          'CRITSITS'[Ticket ID] = 'CROutages'[RTPA] &&
            (SEARCH("Metric",'CRITSITS'[CritSit Event Type],1,-1) > 0)
        ),
        'CRITSITS'[CritSit Event Type]
      )),
"No","Yes")

     

4 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

    try a column

    CritSitVerified = 
var _lookUp = CALCULATE(FIRSTNONBLANK('CRITSITS'[CritSit Event Type], 1), 'CRITSITS'[Ticket ID] = 'CROutages'[RTPA], SEARCH("Metric", CRITSITS'[CritSit Event Type] )> 0 )
RETURN
    IF(ISBLANK(_lookUp),"No","Yes")

     

     

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    You may have better luck with just simple filtering like this:

     

    CritSitVerified = 
    IF(ISBLANK(
      MAXX(
        FILTER(
          'CRITSITS',
          'CRITSITS'[Ticket ID] = 'CROutages'[RTPA] &&
            (SEARCH("Metric",'CRITSITS'[CritSit Event Type],1,-1) > 0)
        ),
        'CRITSITS'[CritSit Event Type]
      )),
"No","Yes")

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello Greg,

       

      You are truly amazing, your solution worked and fix my problem instantly.

       

      Many thanks & have a great weekend 😉

      Don

       

  • candresbq's avatar
    candresbq
    Frequent Visitor
    5 years ago

    Hello everyone.

    I would like to know how you can use the lookupvalue function with a filter, if you have any example it would be very helpful.

    Thank you.