Forum Discussion

Antonio195754's avatar
Antonio195754
Helper IV
3 years ago
Solved

Using lookupvalue for hierarchy filter

Hello,   I'm using the following column formula to return my hiearchy filter  CHANNEL NAME = lookupvalue('Hierarchy Table'[CHANNEL],'Hierarchy Table'[ID],'B.Opportunity'[ID__C])   It works, but...
Need Help
Show and Tell
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    Hi,

    Try this

    =if(isblank(lookupvalue('Hierarchy Table'[CHANNEL],'Hierarchy Table'[ID],'B.Opportunity'[ID__C])),"XYZ",lookupvalue('Hierarchy Table'[CHANNEL],'Hierarchy Table'[ID],'B.Opportunity'[ID__C]))

    Hope this helps.

  • Antonio195754's avatar
    Antonio195754
    3 years ago

    Figured out how to tackle this one.  I just filtered the given outcome i needed to see with a measure, and date a date difference of greater than 0 and put it in a scorecard.  Thanks for the help! Ashish_Mathur 

Antonio195754's avatar
Antonio195754
Helper IV
3 years ago

Ashish_Mathur 

Could you please help with this DAX...

 

I'm wanting to find the date difference where i have a date column and another column that tells me a stage a given sale is in.  STAGE column includes stage "Assigned", "Working", "Won", "Rejected". And the CREATEDATE are the dates from one stage to another.

What i'm trying to do is provide how many days average it takes to move from one stage to another.  Could you please help with that?  I can create a new message as well, but i wanted to reach out to you.

Antonio195754's avatar
Antonio195754
Helper IV
3 years ago

Ashish_Mathur also the order i provided is the sequence the stages should be in.  So how long it takes from assigned to working, from working to won, and won to rejected.

  • Antonio195754's avatar
    Antonio195754
    Helper IV
    3 years ago

    Ashish_Mathur 

    this is what i've tried so far but it says lookupvalue can't work with text to number however ive changed my date column to text and still didn't work.  My stage column is already text.

    DateDiff =
    VAR _PrevDate = LOOKUPVALUE('B Opportunity with History'[CREATEDATE],'B Opportunity with History'[STAGE],'B Opportunity with History'[STAGE]-1)
    VAR _PrevDateClean = IF(_PrevDate=BLANK(),'B Opportunity with History'[CREATEDATE],_PrevDate)
    RETURN DATEDIFF(_PrevDateClean,'B Opportunity with History'[CREATEDATE],DAY)
    • Antonio195754's avatar
      Antonio195754
      Helper IV
      3 years ago

      Ashish_Mathur 

      I'm sorry please disregard that DAX, it won't work b/c my stages are in text and while i tried duplicating the column and assigned values to the stages in the order they should come in (1-4), it still didn't take.  It's saying a table with multiple values was supplied where a single value was expected. Don't think that is the right approach.  Would prefer to use the existing stage names in text format if it's possible.

      DateDiff =
      VAR _PrevDate = LOOKUPVALUE('B Opportunity with History'[CREATEDATE],'B Opportunity with History'[STAGE - Copy],'B Opportunity with History'[STAGE - Copy]-1)
      VAR _PrevDateClean = IF(_PrevDate=BLANK(),'B Opportunity with History'[CREATEDATE],_PrevDate)
      RETURN DATEDIFF(_PrevDateClean,'B Opportunity with History'[CREATEDATE],DAY)
      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        3 years ago

        Hi,

        Share some data and show the expected result very clearly.