Lookup with 1toN connection

[EdList]

Hi,

I am searching since a week but cannot find a working solution. Even if it seems a simple problem.

I have two tables which have a 1 to n relationship:

The first has data about Location. Each location has a ID.

The other table contains reports of visits from those locations with date and customer information. Thus a location can be visited more often.

I wanted to visualize "visited locations" VS "not visited locations" in a bar chart. I thought it would be simple but it did not work.

So I used a lookup-function to check if the location has been visited:

Visited = IF(LOOKUPVALUE(ID_Besuchsbericht_Kunde[PoiID_Split.2],ID_Besuchsbericht_Kunde[PoiID_Split.2],poi[poiId])<>0,"yes","no"

This works fine with the new column.

But now I want to filter in my chart by month and see if a location has been visited f.e. in January.

This is where I am stuck. I thought I can just use a simple relativ date slicer to get this working. However it did not work. Probobly because of the lookup function...

Thus I thought ok then just apply a "AND" statement and make 12 new columns to check if visited "January", "February", ...

This looks like that:

February visited = IF(AND(LOOKUPVALUE(ID_Besuchsbericht_Kunde[PoiID_Split.2],ID_Besuchsbericht_Kunde[PoiID_Split.2],poi[poiId])<>0, LOOKUPVALUE(ID_Besuchsbericht_Kunde[month], ID_Besuchsbericht_Kunde[PoiID_Split.2],poi[poiId])>1),"yes","no")

This does not work because the second lookupvalue return more than one value.

Somehow I am stuck. Can somebody help?

I hope I explained it properly.

Thanks.

Jakob

[Greg_Deckler]

@EdList - It's difficult to understand what is going on from your description. 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.

[EdList]

@Greg_DecklerSorry but my post yesterday has been deleted somehow....

I try to explain in more detail and easier:

I have two tables:

Table 1 - Shops

ID	Shop name	Shop Location	Visited with vlookup
1234	Bradstore	Vienna	yes
4332	Ninastore	Berlin	yes
3212	Samstore	Spain	no

Table 2 - Visits

Salesperson	Visit-ID	Date	ID Store visited
Tom	3223	13-01-2020	1234
Jack	3213	14-02-2020	1234
Sam	3212	01-04-2020	4332
Eva	2123	01-05-2020	1234

So the tables have a 1 to n connection.

I wanted to show in a chart how many stores are visited and how many are not. As a location can be visited more often a normal vlookup would fail. So i just make a new column to check if the store has been visited.

Lookup:

visited = IF(LOOKUPVALUE(table1[id],table2[visit-id],table1[id])=0,"no","yes")

This works fine so far.

My problem is that i want to make a date-slicer to check if a certain location has been visited in a certain month.

Any clue how to get this to work??

Thanks.

Jakob

[mahoneypat]

You are better off getting your result through a measure over a calculated column.  Here are the steps:

1. Make a relationship between your Shops and Visits tables on the StoreID column (1:many from Shops to Visits)

2. Add a table visual with Store ID, Shop Name, and Shop Location and the measure in #3.  Make sure you use the columns from the Shops table.

3. Make a measure like this and add it to the table visual

Visited = IF(ISBLANK(Countrows(Visits), "No", "Yes")

4. Add a Date table to your model, so you can slice by Date info.  Make a 1:Many relationship from it to the Date column of your Visits table. See this article for more details - https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[EdList]

Hi @mahoneypat 

thank you very much for your help.

Your solution is exactely what I had already. However I am happy that you got my problem right 🙂

I check if a store is visited by the this calculated column:

visited = if(ISBLANK(COUNTROWS(RELATEDTABLE(visits))), "no", "yes")

Except for the date table. I added it but the calculated visits visuals still does not change when i filter with the slicer.

Thanks

Jakob

[mahoneypat]

Your last response states that you put your expression in a calculated column.  My response provided a measure expression.  Measures respond to slicer selection, while calculated columns do not.  Please try using the measure expression I provided, along with the other suggestions for tables and relationships.

Regards,

Pat

[EdList]

@mahoneypatThanks. I use a measure now and when using table or a matrix visual it works perfectly fine also when slicing by date!!

I use Shop-ID as value and the measure is used in the quickinfo.

However it fails using a chart visual because it does not show any result. Any idea for this? Than everything would be solved for me 🙂

Thank you in advance.

Jakob

[Icey · Accepted solution]

Hi @EdList ,

You want to show counts in bar chart, right?

If so, you need to create another table with "Yes" and "No".

Then, create the count measure like so:

Measure 2 =
VAR YN =
    SELECTEDVALUE ( 'Table'[Column1] )
RETURN
    CALCULATE ( COUNT ( Shops[ID] ), FILTER ( Shops, [Visited] = YN ) )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EdList]

Hi @Icey 

thanks again for your soluation. Works fine.

However I am struggeling now showing the visits in a bar chart where the visits are splitted by group. My data looks like this now:

I have two tables "Visits" and "Shoplist" as shown on the left side. I applyied your solution and got the bottom right chart which is correct. I also can filter by date which works perfectly fine as well.

What I want now is to visualize the visits splitted by the Shopgroup (chart in the upper right corner).

I tryied several other measures but none work.

Any suggestions?

Thanks.

Jakob

[Icey]

Hi @EdList ,

Please check if this is what you want:

Put "Shopgroup" column into "Axis" field.

BTW, .pbix file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EdList]

Hi @Icey ,

thanks again. Yes this is what I want. However this brings up another problem: I cannot sort by number of shops (number of ID's).

You got a solution for that?

My pbix is available here: https://filebin.net/qd8v5yp216r9cy2u

.

As you can see on the picture the expected result should be EAS>MES>SEP>IAS because I sort by the quickinfo 'number of ID (shops)'. I guess it does not work due to the measure?

Thanks again.

BG Jakob

[Icey]

Hi @EdList ,

Create another measure like so:

Measure = CALCULATE ( COUNT ( Shoplist[ID] ), ALL ( 'YN-Table'[YN] ) )

And then use this measure to sort.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EdList]

Great thanks!!

[EdList]

@Greg_Deckler  Thank you for your reply. Yes I checked the links. I think I am just bad in explaining.

Maybe I just tell you what I want to visualize:

As i mentioned I have two tables:

table 1

ID	Where were you?	Customer	Info	Date
22342	Vienna	Frank	sold him something	2020-01-23
23421	Berlin	Tom	just drank coffee	2020-02-01
22342	Vienna	Frank	sold him something	2020-03-01
22342	Vienna	Frank	sold him something

table 2

ID	Town	visited*
22342	Vienna	yes
23421	Berlin	yes
23431	Spain	no

This is an 1 to n connection.

What I want is to show in a chart which IDs from table 2 have been visited. As I had no clue how to visualize that I just added a lookup-function in table 2:

visited = IF(LOOKUPVALUE(table1[ID],table1[ID],table2[ID])=0,"no","yes")

This worked.

However I thought I could do it without a lookup function but did not know how.

Now I want to have a date slicer. This is what causes me the problem. I did find a way to work around the problem but it is so inefficient that I cannot use it.

There has to be a way to do this easier?

Hope I explained better now.

Thanks.

Jakob