Composite key with filter

[Jug]

Hello, I was wondering if you could help me with this question.

I use directquery and I need to relate two tables with a composite key but using a filter in one of the fields, for example:

The relationship must be made between the CLIENT and ADDRESS tables, between these two tables there is ID_CLIENT in common, an individual code, however in the CLIENT table the code may appear more than once, as it may have changed its contract number, so it is necessary to filter the lines to find the only ID that is still valid classified as "VALID" to form the key. Besides that, in the ADRRESS table the ID_CLIENT can appear more than once as it can be linked to more than one address, since the customer changes addresses over time, so it is necessary to filter the address with the most recent date in the DATE field and then use this field as part of the relationship key between the two tables.   

Finally, we would have the ID_Client as a key, but only the valid ones from the CLIENT table and the most recent ones from the ADDRESS table, to do this I thought of creating a new column with combined values to form a composite key, but I would need to use a filter, how could I use the filter in that case?

This is the part of the code in sql:

....

ON ADDRESS.ID_CLIENT = CLIENT.ID_CLIENT

        AND ADDRESS.DATE_ADR_CLI < CURRENT_DATE

    QUALIFY ROW_NUMBER() OVER (PARTITION BY ADDRESS.ID_CLIENT ORDER BY DATE_ADR_CLI DESC) = 1

    WHERE CLIENT.ID_STA_CLI = 'VALID'

...

[v-jianboli-msft · Accepted solution]

Hi @Jug ,

Please try:

Measure = 
var _a = CALCULATE(MAX('ADDRESS'[UPDATE_DATE]),FILTER(ALL('ADDRESS'),[ID_CLIENT]=MAX('ADDRESS'[ID_CLIENT])))
return IF(MAX('ADDRESS'[UPDATE_DATE])=_a&&MAX('CLIENT'[CONTRACT_STATUS])="VALID",1,0)

then apply it to the visual level filter:

Final output:

Best Regards,

Jianbo Li

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

[v-jianboli-msft]

Hi @Jug ,

What does your source data look like?

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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

[Jug]

I can't share the data, but I tried to exemplify below:

CLIENT= Customers who have a contract

ADDRESS = customers address

ID_CLIENT = individual customer number

In this case I would need to use as the key for the CLIENT table only Ana's ID which has the status of the contract as Valid. In the Address table, I would have to use the Ana's ID that has the most up-to-date address.

Please check if this could help you to understand.

Best regards