how to join 3 tables and calculate distinct values from them

[Anonymous]

Hi,

i have below requirement

i have four tables 

Dim1 (key,id)

Dim2(Key1,Eff_date,Termin_date)

Fact(key,key1)

Date(Start_date,End_date)

Relationship

Dim1[key]=fact[key]

Dim2[Key1]=fact[key1]

Dim2[Eff_date]=Date[Start_date]

Slicers:

1 st is 'after' slicer on start_date and 2 nd slicer is 'before' slicer on end_date

I want to count distinct id of dim1 where effective date and termination date of dim2 are as per user selection

e.g Eff_date<start_date and Termin_date<end_date

I want to create such measure which calculates distinct id based on date filters.

sample data

dim1

key|id

1|5

2|5

3|6

4|6

fact is empty

key1|key

dim2

key1|eff_date|termin_date

101|01-02-2016|01-02-2018

102|02-02-2016|02-02-2018

103|03-02-2016|13-02-2018

date

start_date|end_date

both columns have values from 01-01-2015 to 01-01-2020

[v-chuncz-msft · Accepted solution]

@Anonymous ,

You may take a look at the following posts.

• https://community.powerbi.com/t5/Desktop/Use-a-date-slicer-to-filter-on-a-period-instead-of-a-single-date/m-p/588265#M278953
• https://community.powerbi.com/t5/Desktop/Accumulate-sum-minus-abandoned/m-p/206947#M91174