3 Fact tables with same structure but different usage of dates

[satubuku83]

Dear all,

Considering i have 3 fact tables, each with different date, but similar structure

Claim ID	Underwriting Date	Loss Incurred
1	201801	1000
2	201802	200
3	201803	5000
Claim ID	Accident Date	Loss Incurred
1	201802	1000
2	201803	200
3	201804	5000
Claim ID	Calendar Date	Loss Incurred
1	201803	1000
2	201803	200
3	201804	5000

I am using direct query to database to retrieve this data.

I would like to have a filter that specifies which date view (calander, underwriting, loss). Once i click on the filter, the loss incurred or any other relevant field will reflect accordingly.

Any suggestion besides of union the 3 tables and create the type?

Regards

CS

[Anonymous · Accepted solution]

Create a new table with the union of all and unpivot the Date column in a hack-ish way
Claims =
UNION(
   SELECTCOLUMNS(ClaimTbl_1, "Claim ID", ClaimTbl_1[Claim ID], "Date", "Underwriting Date", "Loss Incurred", ClaimTbl_1[Loss Incurred]),
   SELECTCOLUMNS(ClaimTbl_2, "Claim ID", ClaimTbl_2[Claim ID], "Date", "Accident Date", "Loss Incurred", ClaimTbl_2[Loss Incurred]),
   SELECTCOLUMNS(ClaimTbl_3, "Claim ID", ClaimTbl_3[Claim ID], "Date", "Calendar Date", "Loss Incurred", ClaimTbl_3[Loss Incurred])
)

Original solution found here:
https://stackoverflow.com/questions/50213905/is-it-possible-to-unpivot-in-power-bi-using-dax

[PSBR]

Better you change all date field name as a date after that merge all table into one new table. because of all tables having same fields.

[satubuku83]

I am using direct query, so union cant work unless import?

Thats why asking if there is any other way to do it.

Also, i want a 2 filters, 1 filter to show date range, another filter to differentiate the type(calender view, underwriting view, loss view).

[Anonymous]

Do you have a Date table linking the 3 fact tables up? I would have thought using that Date table as your filter should flow to the other tables.