Multiple active relations

[Adrian_B]

Hello Gents and Ladies, 

today is my first day with BI, i have stuck in creating multiple relations between two tables: 

1. first table is Dynamics -> GL entries, 

2. second table is Excel. 

Columns that match are: 

1. source number, 

2. GL account, 

3. posting date. 

What i want to achieve is to receive from these tables specific amounts for above combinations (like SUM.IF in excel). 

First report shows me of course correct numbers, i have problems with excel file. 

When i`m making relation based on source number, report shows mi YTD amounts from excel amount column for each vendor (source number). 

How to make it right? 

I would be very thankfull for any help. 

And sorry for my english, if more details is needed please let me know. 

Thank you!

Bests

Adrian

[selimovd · Accepted solution]

Hey @Adrian_B ,

thank you for sharing the screenshot. I don't see any date in that table.

Maybe share the tables you want to analyze. Without more information I cannot help you.

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[selimovd]

Hey @Adrian_B ,

yes you can do that by using CALCULATE and then setting some filter conditions or using FILTER function.

If you give some sample data and tell the result you want to archive I can show you how to do that.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Adrian_B]

Hello Selimovd! 

Take a look at below screenshot:

1. First four columns comes from Dynamics GL entries, these are fine. 

2. Last two coumns comes from excel table, which was connected with Dynamics table with source number. My only problem is, that SUM.IF for these column doesnt work, numbers which you can see are YTD postings for reach source number (without included date or GL account).  I would like to make in here SUM.IF, so sums for each line will include posting date, source number and GL account.

thank you, preciate it!

Bests

Adrian

[Adrian_B]

Excel files includes of course posting date (in same format), source number and GL account number.

[selimovd · Accepted solution]

Hey @Adrian_B ,

thank you for sharing the screenshot. I don't see any date in that table.

Maybe share the tables you want to analyze. Without more information I cannot help you.

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Adrian_B · Accepted solution]

Hi Denis, 

Dates are in second column (in month format only) - sorry about polish. 

Anyway, i`m fine now. I realized that i have to add additional tables with dates, chart of accounts and vendors. I have taken from these tables into report dates, vendors and accounts. Numbers were taken from my 2 main reports (excel + GL entries from Dynamics) and it works fine now (as i wanted, report shows me numbers for specific criterias which were taken from these additional tables (like SUM.IF in excel). Excel and Dynamics tables are not linked with each other, made relations only with additional tables.  Criterias to report (dates, vendors and accounts) were taken from additional tables.

If you think i have made something wrong please let me know. 

Thank you very much for your time and patience. 

Preciate it! 

Bests

Adrian