lastnonblank - relating 2 tables via measure to get value

[penningmic]

Dear Forum,

I need to adapt the code from this post to a set of tables

LASTNONBLANK for max 3 days 

In my case, I have 2 related tables rather than 1.  I need to ask of the data for a particular date, what is the call?  More detail below.

I have 2 tables that are related through a 1:many relationship MasterDateTable (1) - (Many) dataTable.

The reference uses a calculated column on a single table.  I am new and learning DAX, and I cannot logic my way through the contexts yet to understand exactly what's going on.  Since I have 2 tables, I think I need to put this into a measure.  And I think it would have a virtual table that is operated on with similar logic to the previosu post (this may be completely wrong!):

myMeasure = 
  var virtualTable = join (DateTable and Table on 'Date')
  Now apply same code from reference on the virtual table

If able to give any help, could you please give a brief explanation of the logic you used such that I can follow your DAX? Thanks!

Ultimately, I need to be able to display a result that shows all the calls on Jan 3rd.  -- even if my dataTable doesn't have a value for that date:

Date	Call	OppProd	Result
1/1/2021 0:00	0	OP-1	0
1/2/2021 0:00		OP-1	0
1/3/2021 0:00		OP-1	0
1/4/2021 0:00		OP-1	0
1/5/2021 0:00		OP-1	0
1/1/2021 0:00	0	OP-2	0
1/2/2021 0:00		OP-2	0
1/3/2021 0:00	2	OP-2	2
1/4/2021 0:00		OP-2	2
1/5/2021 0:00	1	OP-2	1
1/1/2021 0:00	1	OP-3	1
1/2/2021 0:00		OP-3	1
1/3/2021 0:00		OP-3	1
1/4/2021 0:00	0	OP-3	0

I cannot share via OneDrive or upload file via forum, so here is my DataTable, the Date table is just a autocalendar for 2021

Date	Call	OppProd
1/1/2021 0:00	0	OP-1
1/1/2021 0:00	0	OP-2
1/3/2021 0:00	2	OP-2
1/5/2021 0:00	1	OP-2
1/1/2021 0:00	1	OP-3
1/4/2021 0:00	0	OP-3

I adapted @v-alq-msft code for a single table example and works great, but I cannot figure out how to do the above.  Code for reference here.  

res2 = 
COALESCE(
    'Table'[Call],
    var _id=[OppProd]
    var _date=[Date]
    var lastnoblankdate = 
    CALCULATE(
               MAX('Table'[Date]),
               FILTER(
                   ALL('Table'),
                   'Table'[OppProd]=_id&&
                   'Table'[Date]<_date&&
                   'Table'[Call]<>BLANK()
               )
    )
    
    var val=
    CALCULATE(
       SUM('Table'[Call]),
       FILTER(
           ALL('Table'),
           'Table'[OppProd]=_id&&
           'Table'[Date]=lastnoblankdate
       )
    )
  return
      IF(
        [Date]-lastnoblankdate<=1000,
        val,
        0
    ))

Thanks,
Mike

[Jihwan_Kim · Accepted solution]

Hi, @penningmic 

Please correct me if I wrongly understood your question.

Please check the below picture and the sample pbix file's link down below whether it is what you are looking for.

The measure is written in the pbix file.

https://www.dropbox.com/s/3n4hkrd0f822nkq/penningmic.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[penningmic]

Jihwan, 

Thank you for your response.  I am reviewing your sample against my current BI file.  I may have over simplified and your solution exposed that.  However, I may have a work around via my import from SQL.  I may come back with a clarification shortly.  Otherwise will upvote and accept solution.

Thanks again!

Mike

[penningmic]

@Jihwan_Kim ,

Having trouble posting table... will try and get it up here soon

[penningmic]

Hi @Jihwan_Kim,

After reviewing, I wasn't wasn't as inclusive as I needed to be in my data set.  My apologies, I hope we can quickly adapt your code as I have been unable to do so on my own.

I didn't anticipate a few things

1. My OppOppProdHistory_CAS[OppProdHistory_NewValue] field was going to have strings in it (this was my simplified "Call" field from the example).  So, I adjusted your code to use MAX() instead of SUM() to handle a string.  And I tried adding a filter on the FieldType= "Call__c"  but that didn't seem to work.  Still looking for the same result, just that the data table is more complicated than I originally thought.

2. I have another table relationship in between date and NewValue (or Call).

So looks like this:  Data (Many)-(Many) CloseDate (Many) - (1) Date

Thanks,
Mike

[penningmic]

@Jihwan_Kim 

I can't post datatable, giving me HTML error, but still not letting me post after it corrects it...

Tables look like this, see other post with details, sorry for broken up/bad format.

[Jihwan_Kim]

Hi,

Is it possible to share the link to your sample pbix file after deleting the important information?

[penningmic]

@Jihwan_Kim,

I will have to find a way to host the file.  I think the question has changed significantly enough that I need to accept your original solution and post a new quesiton.  I will tag you in next post/question when I can host the file.  Thanks for your help.

Thanks,
Mike