Value from another table

[sunil-kumar5]

Hello All,

I have 3 tables Table1,Table2 and Table3. I have already used left keep on table2 with reference to table1 on my other sheet as dashboard. Now I am making the another dashboard which have the requirement from all three tables. All the three table has columns Name and ID, Also table 3 has unique name and ID only.  I am using one filter for Name from table3 as Name but on my KPI I want when I select any name from table 3, my KPI should reflect count of ID's from table1.

I hope, I am able to make it clear. Please advise.

Thanks

[Prashant_N]

Hi,

As you know about the data of table 3 that its unique. you can directly left join the table 3 with table 1 on the basis of Name & ID by creating a composite key of both.

Like this.

Table 1:

load Name&'-'&ID as Joinkey,Name,ID,Measure from table 1;

left join(Table 1)

Load Name&'-'&ID as Joinkey,Name as table3_Name from table 3;

Hope it helps

Regards,

Prashant

[sunil-kumar5]

Hi Prashant,

Its not giving the desired result, same issue.

Thanks

[Prashant_N]

Please check the data of fields that are used in creating Joinkey. might be the case where key values are not same in both the table.

Regards,

Prashant

[sunil-kumar5]

Table3 has the record of name and id , Table1 has the same name and ID those who received leads from us.

[Prashant_N]

can you provide a dummy data so i can help better

[sunil-kumar5]

Please find the attached sample data for all three tables. I need a count of Lead from table 1 but partner name will be filtered from table 3. Total Count is 18 but if choose any partner name from table3 it should reflect the count as per that.

[sunil-kumar5]

I just checked, based on this sample data I am getting the desired result without any left join but not sure as why it is not coming in my dashboard even I have duplicate the table1 to avoid anything and join separately.

[Prashant_N]

Hi,

This script might help

Table1:
LOAD
PartnerName&'-'&PartnerID&'-'&LeadType as Tb2Joinkey,
PartnerName&'-'&PartnerID as Tb3Joinkey,
PartnerName,
PartnerID,
Lead,
LeadType
// Country
// Zone
FROM [lib://Con/SampleData.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Table2:
Left join(Table1)
Load
PartnerName1&'-'&PartnerID1&'-'&LeadType1 as Tb2Joinkey,
// PartnerName1,
// PartnerID1,
// LeadType1,
LeadStatus
FROM [lib://Con/SampleData.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

table3:
Left join(Table1)
Load
PartnerName2&'-'&PartnerID2 as Tb3Joinkey,
// PartnerName2,
// PartnerID2,
Status,
Country1 as Country,
Zone1 as Zone,
Mobile
FROM [lib://Con/SampleData.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Regards,

Prashant

[Ksrinivasan]

Hi,

table1:
LOAD
PartnerName,
PartnerID,
Lead,
LeadType,
Country,
Zone
FROM ampleData112.xlsx]
(ooxml, embedded labels, table is Sheet1);
Left join
LOAD
PartnerName,
PartnerID,
LeadStatus,
LeadType
FROM SampleData112.xlsx]
(ooxml, embedded labels, table is [Sheet1 (2)]);

table3:
LOAD
PartnerName,
PartnerID as PartnerID_T3,
Status,
Country as Country_T3,
Zone as Zone_T3,
Mobile
FROM SampleData112.xlsx]
(ooxml, embedded labels, table is [Sheet1 (3)]);

In chart Expression:

1. Filter pane DIM as =PartnerName

2. Select KPI and Expression is = Count(Country)

result is blow:

1. without selection

2. if you select Mahender

regards,

Ksrinivasan