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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ilairdyi
Frequent Visitor

How do I create a measure that checks two different tables and a date range from another table

I am trying to create a measure that will check the condition if ID is in two different tables and is in the correct date range which is determined by a separate table?

 

Table 1 has a code, start date, and end date, and is used as a slicer to filter between different codes.

Code 

Start Date 

End Date 

TD1 

10/03/2022 

24/03/2022 

TD2 

16/03/2022 

30/03/2022 

TD3 

11/04/2022 

25/04/2022 

 

Table 2 "ID" has a code and ID. There can be only one unique ID to a code, but the same ID can be in different codes. For example, ID 1 is in TD1 and TD3. Table 1 "code" and Table 2 "ID" have a relationship through the code column, and Table 1 filters to Table 2.

Code 

ID 

TD1 

1 

TD1 

5 

TD1 

2 

TD1 

8 

TD2 

3 

TD2 

4 

TD2 

6 

TD3 

1 

 

Table 3 "Cust" has four columns: code, ID, name, and date. There is a slight problem with this table, as the code can be overwritten with the most recent code for a user. For example, ID 5 has a date between the date ranges of TD1, and as we can see in Table 2, this ID 5 was part of TD1. This is why I am going through the IDs to bring back the correct results/customer. There is a relationship from Table 1 "ID" to Table 3 "Cust" through the ID column, and it's a many-to-many relationship with a single filter to Table 3 "Cust".

code 

ID 

name 

date 

TD1 

1 

John 

15/03/2022 

TD66 

5 

Paul 

18/03/2022 

TD2  

3 

Samantha  

16/03/2022 

TD1 

2 

Mat 

24/05/2022 

TD2  

3 

Jane 

28/03/2022 

 

Table 4 "Trans" holds transactions and has three columns: ID, Amount, and date. There is a relationship from Table 2 "ID" to Table 4 "Trans" through the ID column, and it's a many-to-many relationship with a single filter.

 

ID 

Amount  

Date 

1 

10 

15/03/2022 

1 

22 

18/03/2022 

5 

70 

20/09/2022 

8 

22 

18/03/2022 

4 

100 

20/03/2022 

2 

50 

09/09/2022 

 

This is my current set up and my current result
report.png

The ideal result would be to have the measure should $32 and would only should ID 1 because ID 1 is in Table 3 and Table 4 and within the correct date range from the slicer in Table 1 "code".

AS for the rest of the ID 5, 8  and 2  dont meet the criteria, for example we can see ID 2 and 5 are out of the date range that has been defined from the slicer. ID 8 should not show becuase it is not in table 3 how ever is shows in this measure which i want to change.

This  is my current set up for my modeldata model.png

ideal result would be both matrix and measure to look like this, but mainly to get the measure working correct.

result.png


Here is a copy of my Power Bi file sample.pbix 


1 REPLY 1
lbendlin
Super User
Super User

Consider normalizing your Table 2.  As it is it will give you lots of trouble along the way.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors