RATE table unable to link to SOURCE table

[Anonymous]

Hi All

I have create a RATE table as below :-

I also have a SOURCE table that link to all my AR GL SI INV SUP table.

After i link the RATE table with SOURCE table. The red box indicate that the rate is not link to my SOURCE table :-

May i know how to make it link.

Paul

[v-robertq-msft · Accepted solution]

Hi, @Anonymous 

According to your description and sample pictures, I also created a ‘RATE’ table and I create a one to one and both filter relationship between the two tables, you should also check and keep the relationship active, like this:

Then I created a table chart and I can get your expected table, like this:

And you can get what you want.

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[v-robertq-msft · Accepted solution]

Hi, @Anonymous 

According to your description and sample pictures, I also created a ‘RATE’ table and I create a one to one and both filter relationship between the two tables, you should also check and keep the relationship active, like this:

Then I created a table chart and I can get your expected table, like this:

And you can get what you want.

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[Anonymous · Accepted solution]

Hello Robert

I just read your answer again, and I realize you've already shown me picture where I'm wrong. after checking the red box below, now working well. thank you very much for your help.

[amitchandak]

@Anonymous , You should be able use these(above) measures with Rate dimension table ?

[Anonymous]

Hi Amit

Thank you for your sharing. But my main issue is i need to make my table model correct. then i use the RATE for the rest of the table.

Paul

[StefanoGrimaldi]

do you have relantionship in place for this tables? please show the relantionship diagram, as usually what you would do its reference the rate table by the source table if its linked to your fact table, so 
source table linked to fact table as a dimensional table and rate table linked to source table as a sub dimensional table, with relantionship, or all direct to your fact tables according to how you have your model build. 

[Anonymous]

Hi Stefano

Thank you for sharing , below is my data model :-

[themistoklis]

Hello @Anonymous ,

It seems that you have both an active and inactive relationship for RATE table.

Please read article on link below to understand the difference.

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

For you calculations if you want to use the inactive relationship you should use USERELATIONSHIP

Create the following 2 measure in RATES table and you will notice that the information will be displayed properly

SOURCE_INACTIVE = CALCULATE(MAX('RATE'[SOURCE]), USERELATIONSHIP('RATE'[SOURCE], Source[SOURCE]))

RATE_INACTIVE = CALCULATE(MAX('RATE'[RATE]), USERELATIONSHIP('RATE'[SOURCE], Source[SOURCE]))

[Anonymous]

Hi Themis

Very sorry for give me wrong information on i need multiple relationship. in actual fact when i only need one relationship. Any way now working fine. many thank and wish you all the best in 2021.

Paul

[Anonymous]

Hi Themist

Thank you for your advise.  May i know after create those 2 expression , where should i place them ? 

Paul

[themistoklis]

@Anonymous 

They should be placed in the RATE table

[Anonymous]

Hi Thermistoklis

Thank you very much for your sharing , apprecaite very much.

I have try to create the 2 expression at RATE table .

At first i try with Column it got error , i change to measure error dis-appear. 

But i still see Rate not link to TI and TP :-

Hope you can advise me where i go wrong ?

Paul

[themistoklis]

Hello @Anonymous ,

The formulas should be measures not columns.

You need to do something additionally.

You need to go to data model, edit the relationship between INVC and Source and change Cross Filter Direction to 'Both'. See image below.

Then on the table if you add the following fields the information will be displayed correctly:

SOURCE, SOURCE_INACTIVE, RATE_INACTIVE

[Anonymous]

Hi Themistoklis

I follow your instruction to creae additional step below :-

I see the result still wrong :-

Paul

[themistoklis]

Hello @Anonymous 

Could you also share the workspace with us? Mask any sensitive data

[StefanoGrimaldi]

ok, delete all relantionship from rate table to any fact table and mantain the one to the source table, refere the source table to any fact table or dimensional table using source for any porpuse and make the relantioshio between source table and rate table a 1 to * from rate to source and directo only from rate to source 

let the source table to propagate the rate across all other tables. ensure that the source table its linked with the facts table you needing to have the rate associated to.