topic inaktive Relationships in DAX Commands and Tips

inaktive Relationships

Anonymous — Mon, 15 Jul 2024 08:35:41 GMT

I want to display the linked Export Date (tbl_source) of the Vendor Name (tbl_vendor) and the linked Export Date (tbl_source) of the License Name (tbl_license) , similar to the picture.

In this regard I have tried to write 2 mesures (see below).

However, I would like to have the mesures without the MAX function because it does not show me the linked Export Date but always the largest one. However, Power bi does not allow this. How do I have to change the mesures to get the desired result?

(tbl_vendor has an active relationship with tbl_license, tbl_license and tbl_vendor each has an inactive relationship with tbl_source)

Measures:

As of vendor = MAXX(

VALUES(tbl_license[License Name]),

CALCULATE(

MAX(tbl_source[Export Date]),

USERELATIONSHIP(tbl_source[Source ID], tbl_vendor[Source ID])

)

As of license = MAXX(

VALUES(tbl_license[License Name]),

CALCULATE(

MAX(tbl_source[Export Date]),

USERELATIONSHIP(tbl_source[Source ID], tbl_license[Source ID])

)

The table I want to display:

Tables and Relations:

tbl_vendor

tbl_contract

tbl_source

tbl_license

aktive and inaktiv relationships (the relationships of tbl_source must be inactive because there are many more tables with relationships in my model which are not relevant for the question)

Re: inaktive Relationships

Anonymous — Mon, 15 Jul 2024 06:07:05 GMT

Hi Anonymous ,

Based on your description, we suggest you use the FIRSTNONBLANK to replace the MAX as shown below:

As of vendor = MAXX( VALUES(tbl_license[License Name]), CALCULATE( FIRSTNONBLANK (tbl_source[Export Date],1), USERELATIONSHIP(tbl_source[Source ID], tbl_vendor[Source ID]) ) )

In addition, if the issue persists, we recommend that you upload detailed example data, including table relationships, so that we can test and reproduce it.

Best regards,

Joyce

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

Re: inaktive Relationships

Anonymous — Mon, 15 Jul 2024 08:52:06 GMT

Thanks for your answer joyce. Unfortunately your suggestion does not give me the desired results. I now get the smallest date from tbl_source displayed in all rows.

I have added more images in the original post and hope the question is more understandable now.

For better understanding, here are two examples of what should be displayed (corresponding to the images in the post above):

First line: As of license should be 01.06.23 because Bloomberg Anywhere has the source ID 2 in the table tbl_license. As of vendor should be 12.06.2024 because Blooberg Finance L.P. has the source ID 1 in the table tbl_vendor.

Third line: As of license should be 03.06.23, because Euronext All Indices Package has the source ID 6 in the table tbl_license. As of vendor should also be 12.06.2024 as Blooberg Finance L.P. has the source ID 1 in the table tbl_vendor.

Re: inaktive Relationships

Anonymous — Tue, 16 Jul 2024 09:58:29 GMT

Hi Anonymous ,

Thank you for your additional information.

Per my test, I could not reproduce your issue, please check my .pbix file below:

If the issue still exists, please upload your sample .pbix file for further troubleshooting.

Best regards,

Joyce

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

Re: inaktive Relationships

Anonymous — Thu, 25 Jul 2024 11:57:08 GMT

Thanks a lot for your sample file, my problem was the cross filter direction was only going in one direktion. With both directions it's working!