CODEWITHSUNDEEP
powerbidata-modelingpowerbi-desktoprelationslicers
Florian
Florian

Reputation: 105

Using one slicer (Date) with two different tables and two different data columns

I have several tables in my data model.

Among others I have the table

TABLE "SALES“

Product | Contract ID | Customer ID | Sales Date | Source

A | 001 | C1 | dd.mm.yyyy hh.mm.ss | Source A

B | 002 | C2 | dd.mm.yyyy hh.mm.ss | Source B

B | 003 | C1 | dd.mm.yyyy hh.mm.ss | Source B

C | 004 | C3 | dd.mm.yyyy hh.mm.ss | Source C

D | 005 | C6 | dd.mm.yyyy hh.mm.ss | Source F

TABLE „MEDIA SPEND“

Source | Spend | Campaign Month

Source A | 500 € | mm.yyyy

Source B | 600 € | mm.yyyy

Source C | 300 € | mm.yyyy

Source D | 100 € | mm.yyyy

Source E | 550 € | mm.yyyy

Source F | 1,000 € | mm.yyyy |

The tables are connected by the relation "Source".

It should be mentioned that the "Sales Date" is much more detailed (dd.mm.yyyy hh.mm.ss) than the "Campaign month") (mm.yyyy).

This allows me to filter both customers and marketing budgets by "Source". But at the same time I want to calculate / filter by Date (e.g. „Sales date“). But this is not possible.

How can I proceed to relate the two different columns in different tables?

I have already tried the following

Build the Relation based on both Date-Columns => Problem shift from Date to Source

Second connection (besides "Source") created for the columns "Sales Date" and "Campaign" month. => The data model shows the connection as dashed. Otherwise there is no effect.

THANX!

Upvotes: 1

Views: 6602

Answers (1)

Attie Wagner
Attie Wagner

Reputation: 1362

You can create a dates table by selecting New Table when you're in Power BI as can be seen below:

New Table

Copy and paste the below and your dates table will be created:

DimDate = CALENDAR( DATE( 2018, 1, 1 ) , DATE( 2024, 12, 31 ) )

or

DimDate = CALENDARAUTO( 3 )

Both of which will provide you with the below result:

Expected Result

After creating this table, you can create additional columns using the following dax for each column:

CalendarYear = YEAR( DimDate[Date] )

CalendarMonthInt = MONTH( DimDate[Date] )

CalendarDay = DAY( DimDate[Date] )

CalendarMonthName = FORMAT( DimDate[Date], "mmmm" )

CalendarShortMonth = LEFT( DimDate[CalendarMonthName], 3 )

YearMonth = CONCATENATE( YEAR( DimDate[Date] ), FORMAT( MONTH( DimDate[Date] ), "00" ) )

After adding these columns, your result will look like this:

Final Result

Upvotes: 1

Related Questions