Two inter-related tables with one to many relationship

[Sambath]

Hi Power BI Gurus, 

I'm trying to come up with a good solution to my simple problem illustrated below: 

I have two tables: Table 01 contain only ID and date whereas Table 02 has the ID as many times as the number of vehicles involved. I wanted to be able to count number of accidents for any given month and by vehicle type/manufacturer involved. I used by directional relationship between these two tables (of course with a date dimension table) and it worked but I'm looking for a better solution. 

Any information/direction to previous posts whould be greatly appreciated. 

Table 01
AccidentID	OccurredDate
A_001	5/02/2024
A_002	1/03/2024
A_003	6/01/2024
Table 02
AccidentID	VehicleType	Manufacturer
A_001	SUV	Toyota
A_001	Hatch	Ford
A_002	Sedan	VW
A_002	Sedan	Toyota
A_002	Van	BMW
A_003	SUV	Jeep
A_003	SUV	Toyota

[Anonymous]

Hi @Sambath ,

It seems that you have gotten a solution. Could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards,

Clara Gong

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

[Sambath]

Thanks for all the valuable suggestons. I've tested CROSSFILTER function and it worked well. 

[Anonymous · Accepted solution]

Hi @Sambath ,

I’d like to acknowledge the valuable input provided by the @amitchandak . Their initial ideas were instrumental in guiding my approach. However, I noticed that further details were needed to fully understand the issue. 

In my investigation, I took the following steps: 

1. Create a date table.

DAX DateTable = 
ADDCOLUMNS (
    // CALENDAR(DATE(2024,1,1), DATE(2024,12,31)),
    CALENDARAUTO(),
    "Year", YEAR([Date]),
    "Quarter", "Q" & FORMAT(CEILING(MONTH([Date])/3, 1), "#"),
    "Quarter No", CEILING(MONTH([Date])/3, 1),
    "Month No", MONTH([Date]),
    "Month Name", FORMAT([Date], "MMMM"),
    "Month Short Name", FORMAT([Date], "MMM"),
    "Month Short Name Plus Year", FORMAT([Date], "MMM,yy"),
    "DateSort", FORMAT([Date], "yyyyMMdd"),
    "Day Name", FORMAT([Date], "dddd"),
    "Details", FORMAT([Date], "dd-MMM-yyyy"),
    "Day Number", DAY ( [Date] )
)

2. Create a relationship between them.

3. Create measures.

CountByMonth =
CALCULATE (
    COUNT ( 'Table2'[Manufacturer] ),
    FILTER (
        ALL ( 'Table1' ),
        MONTH ( 'Table1'[OccurredDate] ) = MAX ( 'DAX DateTable'[Month No] )
            && 'Table1'[AccidentID] = MAX ( 'Table1'[AccidentID] )
    )
)

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[amitchandak]

@Sambath , You can add date table and join it with Table1 and use the month, year etc as filter from date.  All join can be single directions.

Better one is

In Power query Merge Table 1 into table and keep date in table 2

Now Join Table 1 and Date with Table 2 , 1-Many(Single directional )\

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.