Dax Power BI help to filter based on 2 columns

[Anonymous]

Hello All,

I've SQL data as:-

CREATE TABLE #Temp1 ( app_dt date, borr_type varchar(10) )
CREATE TABLE #Temp2 ( app_dt date, borr_type varchar(10), mt smallint )

INSERT INTO #Temp1 VALUES ( '2020-11-01', 'Nw' )
INSERT INTO #Temp1 VALUES ( '2020-11-01', 'Pr' )
INSERT INTO #Temp1 VALUES ( '2020-11-01', 'Fr' )
INSERT INTO #Temp1 VALUES ( '2020-12-01', 'Nw' )
INSERT INTO #Temp1 VALUES ( '2020-12-01', 'Pr' )

INSERT INTO #Temp2 VALUES ( '2020-11-01', 'Nw', 123 )
INSERT INTO #Temp2 VALUES ( '2020-11-01', 'Pr', 456 )
INSERT INTO #Temp2 VALUES ( '2020-11-01', 'Fr', 789 )
INSERT INTO #Temp2 VALUES ( '2020-12-01', 'Nw', 147 )
INSERT INTO #Temp2 VALUES ( '2020-12-01', 'Pr', 258 )

SELECT	*
FROM	#Temp1

SELECT	*
FROM	#Temp2

DROP TABLE #Temp1
DROP TABLE #Temp2

Join is between app_dt between these 2 tables under model.

Now I want to display Matrix visual which is build from the #Temp2 to filter based on the borr_type. Borr_type (3 distinct values) is the hierartical slicer.

Need your help to with DAX query.

TIA

Regards,

gk03

[amitchandak]

@Anonymous , Ideally both table should be joined with date table and common borr_type type table .They should not join

create borr_type 

Bridge Table: https://www.youtube.com/watch?v=Bkf35Roman8&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=19

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.

Try to be in star schema -https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/

[Anonymous]

@amitchandak here is my sample .pbix file link,

https://drive.google.com/file/d/1SvAsXd2T3mm0qmJZFgl1gXYT_1KpNhxP/view?usp=sharing

Let me know how to connect below borr_nm slicer with the Matrix visual?

TIA