Relate tables on several columns in a DAX measure

[eyesem]

Hi

I have these two unrelated tables:

Price_table

Client	Date_start_price	Date_end_price	Price
1	01/01/2021	31/01/2021	5
1	01/02/2021	31/03/2021	6
2	01/01/2021	30/06/2021	7
2	01/07/2021	31/12/2021	4

Discount_table

Client	Date_start_discount	Date_end_discount	Discount
1	01/02/2021	28/02/2021	1
1	01/03/2021	31/03/2021	4
2	01/03/2021	30/04/2021	2
2	01/06/2021	31/9/2021	3

I will always filter the Price table for an specific date (ie start<01/2021 && end >01/02/2021) and I need to add a measure with the discount applying on that date:

Price_Table (filtered start<01/2021 && end >01/02/2021)

Client	Price	@Discount
1	5	1
2	7	0

¿How can I get that?

Thanks.

[v-robertq-msft · Accepted solution]

Hi, @

According to your description, I can roughly understand what you want to get, you want to filter the Price table for a specific date and get the discount based on the start and end date, right? I think you can achieve this using measures and visual filter in Power BI, you can try my steps:

1. Create a Calendar table for the date slicer:

Date = CALENDARAUTO()

2. Create two measures:

Flag =

var _selectperiod=

SELECTCOLUMNS(ALLSELECTED('Date'),"1",[Date])

return

IF(

    MAX('Price_table'[Date_start_price]) in _selectperiod||

    MAX('Price_table'[Date_end_price]) in _selectperiod,

    1,0

)

Dicsount1 =

var _value=

CALCULATE(

    MAX('Discount_table'[Discount]),

    FILTER(

        ALL(Discount_table),

        [Date_start_discount]<=MAX([Date_start_price])&&

        [Date_end_discount]>=MAX([Date_end_price])&&

        [Client]=MAX([Client])))

return

IF(_value=BLANK(),0,_value)

3. Create a Slicer and place ‘Date’[Date], and a table chart then place it like this:

4. Then add a filter to this filter, like this:

And you can get what you want.

You can download my test pbix file here

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result so that we can help you in advance.

How to Get Your Question Answered Quickly 

Thank you very much!

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.

[eyesem]

Thanks I did some slight changes and it works wonderfully!

[negi007]

@eyesem 

in this case, there are few things that you will have to do

1) create a date table which will be used to filter data by date and link two tables

Data_Tab = CALENDARAUTO()

2) create one more table that will have unique values of ClientIDs from Pricing and Discount table

Client = DISTINCT(UNION(DISTINCT(Discount_Tab[Client ]),DISTINCT(Price_Tab[Client ])))

3) then you create relationship between tables like below

4) below is the visual you want to have here

I have attached pbix file as well

thanks. let me know if helps you

[eyesem]

Thank you

Thank you @negi007 

This works fine for the months where the price starts, but not for those in between. For example August should display a price of 4 and discount of 3 (client 2), but there is no start date for august in the tables, that is the tricky thing here.