how to use custom created column in LOOKUPVALE function as a search parameter

[Krunalbpatel]

Hello,

I want to use a custom created column in LOOKUPVALUE function to get the my required result. 

I have 2 seperate SharePoint Online lists from different sites. In the first list YEAR,MONTH and Days fields, Now in the second list I have a date column. I want to display Month-Year(January-2017) in the x-axis. Now in calculation I want Days from the first list in the second list, How can I get the Days? In both lists I have created custom column(MonthYear) to generate January-2017,February-2017

To achieve this I am trying to use LOOKUPVALUE function, but in that I am not using custom created column for search.

I am trying like this

NumberOfDays = LOOKUPVALUE('List A'[Days],'List B'[MonthYear],'List A'[MonthYear])

Am I using the correct funtion to achieve this?

Please help me. 

[TomMartens]

Hey,

I'm not sure if I understand the structure of your table, but you may try this, create a calculated column in the table where you need the days.

NumberOfDays = 
var targetMonthYear = 'targetTable'[MonthYear]
return
CALCULATE(
  COUNT('sourceTable'[Days])
  ,'sourceTable'[MonthYear] = targetMonthYear
)

It may be necessary to use SUM(...) instead of COUNT(...)

If both won't work for you it would be helpful if can share a link that points to a pbix on onedrive or dropbox with sample data

Regards

Tom

[Krunalbpatel]

@Anonymous, Thanks for the reply

I don't want count or sum of days as a result , I want the exact value in Days column.

[TomMartens]

Hey,

If there is just one row for each MonthYear in the sourceTable, then SUM() will return the exact value. But it seems it will be helpful if you can share sampledata.

Regards

Tom

[Krunalbpatel]

FYI - I am creating measure, and I am not able to select the custom created column in the variable called "targetMonthYear"

NumberOfDays = 
var targetMonthYear = 'targetTable'[MonthYear]
return
CALCULATE(
  COUNT('sourceTable'[Days])
  ,'sourceTable'[MonthYear] = targetMonthYear
)

[TomMartens]

Hey,

as I mentioned, my DAX statement will work if you would created a Calculated Column.

It's seems to become difficult talking about your issue w/o knowing exactly how your table structure looks like, please share sample data also mock up how your target table should look like and how each table will be used on a report.

Regards

Tom

[Krunalbpatel]

Ohh Yes,

You mention that, I want to create measure. Is there any other option to create measure like this?

[TomMartens]

Hey,

please provide a link to a pbix file that contains sample data on onedrive or dropbox.

Regards

Tom

[Krunalbpatel]

Hi,

Please find the link below

https://drive.google.com/open?id=0B2-qjP7KVvO3NjdoMmprVi1WVEE. 

In Source table I want a measure that will get the Days from the Target. 

Measure = [NumberOfRecord]/Target[Days])

the exaclty I am trying to achieve, But Somehow I am not getting the Target[Days]

[KimKnudsen]

Hi,

I have  a suggestion, in linked sample file. One solution with lookupvalue (works with one row per month year), One for multiple row doublet rows - take a look.

Link to Sample File

Kind regards,

Kim

[Krunalbpatel]

Thanks for the reply. But my solution is not resolved with this. I have to use Days in my calculation, not display like this. So It not worked for me with calculated column. Is this possible?

I want number of records in August-2017 / Days (where August-2017) from the Target table. The above image is for reference, the calculation is not correct

[KimKnudsen]

Hi again,

You might as well just do it in a measure, like this:

Number Of Record Divided By Number Of Days From Target =
DIVIDE (
COUNTROWS ( Source );
CALCULATE (
FIRSTNONBLANK ( Target[Days]; 1 );
FILTER ( Target; Target[Months] = CALCULATE ( VALUES ( Source[Months] ) ) )
)
)

You only need the calculated column when you actually need it as a axis, row or column in a visualization, as a rule of thumb.

Check out sample file