Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lcasey
Post Prodigy
Post Prodigy

Urgently Need assistance with relating Data in Power BI

Hello,

 

I am hoping someone can assist me figure out how to complete the report I am developing.  The report is an Aging Report that needs to show Multi Currency from a table as of the exact date chosen by the end user and put that exchange rate into the summary for the customer record.

 

I have two Datasets:

 

BSA00-MC - This is a Multi Currency table that has Curency ID, Exchange Rate, and Exchange Date

BSA00-RMMC - This is a dataset pulling in 89k individual records from our Accounts receivable module.  It includes every single invoice and every single payment for each customer.

 

I have successfully made the report so that if an end user chooses a date, they can go back in time and see the correct balances of those customer records as of the date they choose.

 

What I need to do, is on all of those Open balances , be able to match the currency ID , with the Exchange rate as of the specific date the user chooses. However The exchange Rate SUMS up and gives the wrong exchange rate.

 

What I need is a Vlookup()  function to look at the current Currency ID, look into the BSA00-MC table and extract the exchange rate for that Particular currency ID.

 

 

I have two filters on the page level of the report. 

 

First Filter - Posting Date from BSA00-RMMC is <= The date I put in the filter

Second Filter -  Exchange Date from BSA00-MC = The exact date I enter in the filter.

 

The Problem is I can not create a relationship in SQL as the Currency rate is determined by the date the users chooses when running the report. So I need to Bring Both datasets into Power BI.

 

IF I relate the Records on Currency ID, then the balances are all wrong becouse the filter on MultiCurrency limits the recordset.

 

I only need the Exchange Rate on the TOP table where I have filtered out all 0 balances so that the Exchange rate is calculated JUST on the open balances as seen in the TOP table.

 

Can this even be accomplished in Power BI?    It Works well in SSRS becouse I can use a parameter when loading the dataset and also filter out all Exchange Rates based on the report date.

 

 

 

 

MCIssue.png

 

 

1 ACCEPTED SOLUTION
Vvelarde
Community Champion
Community Champion

@lcasey

 

Using your data without changes

 

Create this measure

 

EXCHRATETOUSE =
VAR EXCHDATETOUSE =
    MIN ( 'BSA00-MC'[EXCHDATE] )
RETURN
    IF (
        HASONEVALUE ( 'BSA00-RMMC'[CUSTNMBR] ),
        LOOKUPVALUE (
            'BSA00-MC'[XCHGRATE],
            'BSA00-MC'[CURNCYID], VALUES ( 'BSA00-RMMC'[CCURNCYID] ),
            'BSA00-MC'[EXCHDATE], EXCHDATETOUSE
        )
    )



Lima - Peru

View solution in original post

12 REPLIES 12
lcasey
Post Prodigy
Post Prodigy

I am also unable to create a relationship becouse Power BI does NOT allow a Many to Many relationship

 

MCIssue2.png

Vvelarde
Community Champion
Community Champion

 

hi @lcasey

 

Try with this and modified accord to your needs

 

ExchangeRatetoUse =
LOOKUPVALUE (
    'BSA00-MC'[Exchange Rate],
    'BSA00-MC'[CurrencyID], VALUES ( 'BSA00-RMMC'[CurrencyID] ),
    'BSA00-MC'[Exchange Date], VALUES ( 'BSA00-MC'[Exchange Date] )
)

 




Lima - Peru

Thank you for the reply.

 

Unfortunatly it did ot work.   I am thinking that Power BI just cant handle filtering data based on report date.

 

error.png

 

 

 

Vvelarde
Community Champion
Community Champion

@lcasey

 

For Testing: Try Disabling the Total of your table i believe that the error message dissapear.

 

One more thing: ¿Do you have duplicates combination of currencyId and Dates?




Lima - Peru

So there are duplicates. 

 

The BSA00-RMCC table contains 89k records of wich some records have a Curency ID and Others Dont.

 

BSA00-MC  table contains 700K records and includes the exchange rates for every single day , for every single currency id.

 

I have also tried creating a single table with unique Currency ID's

 

Here is a Short Video:

http://screencast.com/t/Q04fO9qsmGSF

 

The Problem is that top Table MUST remain at 637 records and never change. I just need that top table to show the correct Echange Rate AS of The Report date the end user chooses.

 

Anything I have tried will Increase the record count on that top table.

The Problem is that the

 

Vvelarde
Community Champion
Community Champion

@lcasey

 

If you deleted the column Count of your table remain the 3K Rows?

 

Can you share your PBIX file changing sensitive-data. This would be more fast to try to replicate your scenario.




Lima - Peru

Hello,

 

Sure, It may take some tim for me to get a clean file to you.  I really appreciate you looking at this.

 

The Ultimate Goal is to get the correct exchange rate to show up in the Exchange rate on the top table:

 

The Top Table is just filtered so no 0 Balance will show up. Only Customers with open balances should show up on the top table.

 

If I filter the Multi Currency Table, I get the correct exchange rates. But that Filter is not honored for any formulas.

 

Ill do a clean up of the Data so no sensitive data is there and upload

 

Thanks

 

issue.png

Hello,

 

The PBIX file is here:

 

Let me know when you have it.        Bad Debt PBIX File

 

 

Vvelarde
Community Champion
Community Champion

@lcasey

 

Using your data without changes

 

Create this measure

 

EXCHRATETOUSE =
VAR EXCHDATETOUSE =
    MIN ( 'BSA00-MC'[EXCHDATE] )
RETURN
    IF (
        HASONEVALUE ( 'BSA00-RMMC'[CUSTNMBR] ),
        LOOKUPVALUE (
            'BSA00-MC'[XCHGRATE],
            'BSA00-MC'[CURNCYID], VALUES ( 'BSA00-RMMC'[CCURNCYID] ),
            'BSA00-MC'[EXCHDATE], EXCHDATETOUSE
        )
    )



Lima - Peru

You are a Amazing!

 

That absolutely worked exactly as I needed.  I  am speechless and thank you very much!!!

Thank you for the reply.

 

Unfortunatly it did ot work.   I am thinking that Power BI just cant handle filtering data based on report date.

 

error.png

 

 

 

Anonymous
Not applicable

may you can use VALUES function 

check this post out

https://msdn.microsoft.com/en-us/library/ee634547.aspx

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.