Using lookupvalue for hierarchy filter

[Antonio195754]

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 what i'm trying to include within the formula is if it returns any blanks, to name those blanks "XYZ" (example)

I'm thinking i have to wrap it around an IF(ISBLANK), but i'm not having any luck with it.  Is this possible to do?

Thank you

[Ashish_Mathur · Accepted solution]

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.

[Ashish_Mathur · Accepted solution]

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]

That worked perfectly, thank you!!!

[Ashish_Mathur]

You are welcome.

[Antonio195754]

@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]

@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]

@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]

@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]

Hi,

Share some data and show the expected result very clearly.

[Antonio195754]

hey @Ashish_Mathur , Turns out the client changed the data layout... But I think it may be easier to make work with the new layout.  Below is the link to CSV file of a sample.  

Please note that each Opportunity ID is the sale ID.  You will see duplicates of the ID because every time an ID changes from one "VALUE" (the Stagename) to another, a new row is created.  You'll see OLDVALUE and NEWVALUE.  OLDVALUE is just that, the old value before it was changed to the NEWVALUE.  CREATEDDATE is the Date/Time that VALUE/Stagename was changed.  There are blanks within the OLDVALUE and NEWVALUE that i think are key to get the date difference from one stage to another using the CREATEDDATE column.  The table is a logging of any change to a field. Therefore, if the old value is blank/null, then it never had a value and the create date on that row is the first time that field has been populated. Here's an example below link to file, looking at one specific Opportunity ID, where we can see all of the tracked changes for this specific Opportunity ID...and I'm highlighting in the results that there is a row logged for a change to the field 'Created'.

I really am only concerned when the OLDVALUE/NEWVALUE have gone from OLDVALUE Assigned, to NEWVALUE Working (Working is an aggregate of Working and Working -Contacted for both the OLDVALUE AND NEW VALUE columns), OLDVALUE Working to Closed Won, and Working to Rejected (Rejected is an aggregate of Rejected, Recycled, and Retired for both the OLDVALUE AND NEWVALUE columns).  

This would have been much easier if there was a column that showed the OLDVALUE createddate as i could do a date difference of it to the NEWVALUE createddate, but not the case unfortunately.  

Any help would be much appreciated!

https://drive.google.com/file/d/17ibHR1NsayUsqBK9iXEKvVq5ch0j0NLe/view?usp=sharing

[Ashish_Mathur]

Hi,

Show the expected result in another worksheet of the Excel workbook.

[Antonio195754]

Hi @Ashish_Mathur I added another tab with the desired outcome.  I'm wanting to show these days difference average in scorecards within PBI with their respective headers.  Below is the screenshot of the new tab i created.

[Ashish_Mathur]

Hi,

I do not see a second tab in that workbook.  In that new tab, please also explain how you got the result.

[Antonio195754 · Accepted solution]

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]

The stage flows i'm looking for are when an oldvalue goes from Assigned to newvalue working, oldvalue working to newvalue won, and oldvalue working to newvalue returned (i've already aggrated the stages that need to be considered returned).  You'll notice that the oldvaluecreatedate will, for the most part, always be an earlier time stamp when compared to the newvaluecreatedate.  So i'm trying to get the days difference between those createdate columns, based on the oldvalue/newvalue stage outcomes i laid out above.