Column Chart with Date in Axis & Slicer Not working

[achyutmishra92]

Hi,

There are two tables i have

Invoice Table(Table1) : Invoice wise (Invoice Number Column) details with Company Column (Multiple Companies) & Invoice Date Column

Receipt Table(Table 2): Receipt wise (Receipt Number Column) detailes with Company Column (Multiple Companies) & Receipt Date Column

I have created a custom calendar table to which i have created a relationship to both table 1 & table 2 and the chart is working fine.

I also have a slicer for Companies at the top of my report. 

The problem starts when i choose one company from the slicer, data gets updated for Gross Amount (coming from Invoice Table) and not for Receipt amount (coming from Table 2) as there is no relatonship between companies in two different tables.

When i try to create same, i am getting an ambiguity error.

Any solution on how to achieve this ? @AM @needhelp 

[achyutmishra92 · Accepted solution]

Well this is what i did and it worked for me!

I created a active relationship between two tables through a master company table as shown below

 After this to calculate the Invoice amount and the receipt amount, I created a calculated measure in both the entries table & RC_Datalake Table as below

1. Gross Amt Graph = CALCULATE(

SUM(entries[Gross Amount (AED)]),

USERELATIONSHIP(entries[Column1.Invoice Date],'Calendar'[Date]))

2. Receipt Amount Graph = CALCULATE(

SUM(RC_Datalake[Receipt Amount (AED)]),

USERELATIONSHIP(RC_Datalake[Column1.Receipt Date],'Calendar'[Date]))

Kindly note that this formula only worked when i created an inactive relationship between Calendar table & the tables in question.

Now the data is working fine while using slicers for companies as well as on the timeline column chart since they are connected through an inactive relationship.

Hope this can be used by all.

[jdbuchanan71]

@achyutmishra92 

You shouldn't need an inactive relationship to your calendar table, the measures should be simply this:

Gross Amt Graph = SUM(entries[Gross Amount (AED)])

Receipt Amount Graph = SUM(RC_Datalake[Receipt Amount (AED)])

[jdbuchanan71]

@achyutmishra92 

You can create a companies table that sits above both of your fact tables and links to the company field.  Then you use the company from the new lookup table in your slicer.

Companies =
DISTINCT (
    UNION ( 
	DISTINCT ( Table1[Company] ), 
	DISTINCT ( Table2[Company] )
    )
)

[achyutmishra92]

Tried doing that! The slicer created from new table is not working for any of the items in my report.

[achyutmishra92]

This company table with say two columns by "Company Number" & "Company Nmae" is created. 

Do i need to create relationship of this new table to the existing tables. If yes, it will give me same ambiguity error as there is already one relationship which exists.

[jdbuchanan71]

You would join it on company number since that is probably the unique one. Could you share what the new company table has in it, they should be uniques unless youhave the same company number with different names like:

123 - ABC

123 - ABC Inc