DAX to lookup text values in another table

[Stives93]

Hello,

I have created a PBI report for tracking mileage for expense claims, which uses a SharePoint Online list as its data source,.

A table called Venues contains post codes locations which are travelled from and to.

A table called Journeys contains the columns From.lookupId  and To.lookupId, each of which references the same column called VenueID in another table called Venues .

This is by design, as I do not want the table used to store each individual dates journey (called Claims)to store from and to locations in a calculated column for what will be thousands of the same locations where they are individually logged on a daily basis on a mileage claim – hence used Id fields and did not select the text values for inclusion in the Journeys table when modelling the data in Query Editor.

Instead, my plan is to use the integer value to store the location and to create a measure which looks up the venueID field and returns the corresponding venue name from the Venue table so that it is only shown when a slicer filter is applied to view a particular expense claim report.

Calculated columns using the following syntax return the correct result:

From =LOOKUPVALUE(Venues[Venue], Venues[VenueID], Journeys[From.lookupId])

To =LOOKUPVALUE(Venues[Venue], Venues[VenueID], Journeys[To.lookupId])

Whilst I do understand the fundamental differences between a calculated column and a measure, just cannot work out how to get this to work as a measure.

Various examples that I’ve looked at trawling through countless articles/videos all seem to be wrapped around a result related to calculations, whereas I don’t need a calculation – just a text value to be returned.

Clearly more study needed but in the interim, any help would be greatly appreciated.

[tamerj1]

Hi @Stives93 

please test the following 

From =
IF (
    HASONEVALUE ( Clams[TravelDate] ),
    VAR T1 =
        ADDCOLUMNS ( Claims, "@LookupID", RELATED ( Journeys[From.lookupId] ) )
    VAR T2 =
        DISTINCT ( SELECTCOLUMNS ( T1, "@@LookupID", [@LookupID] ) )
    RETURN
        CONCATENATEX ( T2, [@@LookupID], " " )
)

[Stives93]

Thanks @tamerj1 - just tried that and it's returning the ID # rather than the text value.

[tamerj1]

Hi @Stives93 

what do you want to return, which column?

[Stives93]

Hi @tamerj1 - I am wanting the From and To column to return the text value in the Venues table, from column name Venue, based on the FromLookupId and the ToLookupId for a particular journey.

[tamerj1]

@Stives93 

Please try

From =
IF (
    HASONEVALUE ( Clams[TravelDate] ),
    VAR T1 =
        ADDCOLUMNS ( Claims, "@LookupID", RELATED ( Venues[Venue] ) )
    VAR T2 =
        DISTINCT ( SELECTCOLUMNS ( T1, "@@LookupID", [@LookupID] ) )
    RETURN
        CONCATENATEX ( T2, [@@LookupID], " " )
)

[tamerj1]

@Stives93 

Any luck? Please don't get confused with the wrong names I gave to the virtual columns. This does not affect the result. Shall fix it later. 

[Stives93]

thanks very much for all your help with this @tamerj1 - please excuse my ignorance but I can't see what differentiates the from and to lookup in the measure example that you gave. Perhaps this is what you were referring to by the virtual columns but over my head I'm afraid.  I pasted it into 2 measures and as expected get the From location in each at the moment.  Screen print attached..

[tamerj1]

@Stives93 

In the 2nd measure you need to use RELATED ( Venue[Venue Id] )

[Stives93]

Tried that but it returns the ID of the From venue, I am wanting to return the Venue for the To venue from the corresponding TolookupId. Currently both the From Venue and To venue are returning the same result (depending on whether Venue or VenueID is used of course).

It may be that I've not explained clearly what is happening - but for context, I have one list of locations in the venue table.

The journeys table lists all regular journeys from and to and their distance, with full post codes - so when logging a claim, the user selects the journey from an abbreviated description, which be Birmingham to London for example, the journey table holds the record of the From and To location based so in the visualisation I want to be able to show the actual full From and To location in the report, based on its description in the Venue table.

[tamerj1]

@Stives93 

Any chance you can share the sample file? You can upload to any file transfer service and share the download link. 

[Stives93]

Hi - Have stripped down the data and put together a sample file here:

https://www.dropbox.com/s/m9oosz32rbf5v8z/PBI_MeasureLookupTableValues.pbix?dl=0

[tamerj1]

Hi @Stives93 
The solution has been updated in the previous reply. Please refer to the attached amended file.

[Stives93]

Hi @tamerj1 - it's still duplicating when I refresh the data.  I added the journey ID column to the visual so you can see that it is showing every journey as the same  (id 30) which does not reflect the entries in the Claims table.

Have attached screen prints of each to illustrate this.

[tamerj1]

@Stives93 
The solution is updated agian in the same reply. Please check.

[Stives93]

Hi @tamerj1 - just downloaded the updated version. The visual still shows all 5 journeys as the same journey (journey ID 30) but as per the Data view, they are not the same. 3rd & 5th October entries are for Journey ID 31.  I greatly appreciate all the time you have spent on this and rather than going back and forth any longer, I will just use the text value from the query editor and forget using an ID lookup. Thanks again.

[tamerj1 · Accepted solution]

@Stives93 
Apologies for the late reply, I didn't have access to my PC.

PLease refer to attached sample file with the solution

From = 
CALCULATE ( 
    SELECTEDVALUE ( Venues[Venue] ),
    TREATAS ( { SELECTEDVALUE ( Journeys[FromVenueID] ) }, Venues[VenueID] ),
    CROSSFILTER ( Venues[VenueID], Journeys[FromVenueID], BOTH )
)

To = 
CALCULATE ( 
    SELECTEDVALUE ( Venues[Venue] ),
    TREATAS ( { SELECTEDVALUE ( Journeys[ToVenueID] ) }, Venues[VenueID] ),
    USERELATIONSHIP ( Venues[VenueID], Journeys[ToVenueID] ),
    CROSSFILTER ( Venues[VenueID], Journeys[ToVenueID], BOTH )
)

[Stives93]

Morning @tamerj1 - thanks again for all your help with this. Although it appears now to correctly display the venue based on the sample data, when additional data is included, it is duplicating each record. I have added some additonal entries to the sample data to illustrate this (and a report title field for clarity).

https://www.dropbox.com/s/m9oosz32rbf5v8z/PBI_MeasureLookupTableValues.pbix?dl=0

[Greg_Deckler]

@Stives93 You should just have to use an aggregator like this:

From Measure =LOOKUPVALUE(Venues[Venue], Venues[VenueID], MAX(Journeys[From.lookupId]))

To Measure =LOOKUPVALUE(Venues[Venue], Venues[VenueID], MAX(Journeys[To.lookupId]))

You can also always use the alternative to LOOKUPVALUE, MAXX(FILTER(...),...). 

If none of this helps, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Stives93]

Thanks for the response Greg.

I tried the measure that you suggested but it is returning the same from and to location for every journey.

To more clearly illustrate the issue, I have annotated some screen prints, based on sample data for the same journey on two travel dates. Hope this makes sense.

Current resultRelationshipsDesired resultContext