Filtering between unrelated tables

[Mezga]

Hello,

Appreciate if someone could please provide some help on the below:

I have two tables, the first is a large list of documents. There are many columns for other fields, e.g. start date, finish date, discipline, and so on. Below is a very small example of TABLE 1:

DOCUMENT NUMBER	START DATE	FINISH DATE	DISCIPLINE
DOC-1	01/01/22	03/01/22	MECHANICAL
DOC-2	02/01/22	04/01/22	STRUCTURAL
DOC-3	02/01/22	05/01/22	ELECTRICAL
DOC-4	02/01/22	04/01/22	ELECTRICAL

The second table is a list of dates, lets say from 01/01/22 to 31/12/22. It has several custom columns that make calculations on the first table, based on what date it is. TABLE 2:

DATE	STARTED DOCUMENTS AS OF DATE	FINISHED DOCUMENTS AS OF DATE
01/01/22	1	0
02/01/22	4	0
03/01/22	4	1
04/01/22	4	3
05/01/22	4	4
And so on to 31/12/22	...	...

Now that all my visuals are set up and working correctly, I wanted to be able to apply filters based on various fields from Table 1. Table 2 will still have the full range of dates, from 01/01/22 to 31/01/22, but I need the calculated columns of Table 2 to only look at filtered values of Table 1. 

For example, I need a filter to look at only the "Electrical" discipline from Table 1. There are many other similar filters I need to create.

However since there's no columns in the two tables that are directly related, I can't create a relationship make a slicer work in the usual fashion.

Any ideas?

[v-yangliu-msft]

Hi  @Mezga ,

Here are the steps you can follow：

1. Create measure.

mindatestart =
var _select=SELECTEDVALUE('Table1'[DISCIPLINE])

var _mindatestart=CALCULATE(MIN('Table1'[START DATE]),FILTER(ALL(Table1),'Table1'[DISCIPLINE]=_select))
return
_mindatestart

mindatefinish =
var _select=SELECTEDVALUE('Table1'[DISCIPLINE])
var _mindatefinish=CALCULATE(MIN('Table1'[FINISH DATE]),FILTER(ALL(Table1),'Table1'[DISCIPLINE]=_select))
return
_mindatefinish

STARTED DOCUMENTS MEASURE =
IF(
    MAX('Table2'[DATE])<[mindatestart],0,
COUNTX(FILTER(ALLSELECTED('Table1'),'Table1'[DISCIPLINE]=SELECTEDVALUE('Table1'[DISCIPLINE])&&'Table1'[START DATE]>=[mindatestart]),[DOCUMENT NUMBER])
    )

FINISHED DOCUMENTS MEASURE =
IF(
    MAX('Table2'[DATE])<[mindatefinish],0, COUNTX(FILTER(ALLSELECTED('Table1'),'Table1'[DISCIPLINE]=SELECTEDVALUE('Table1'[DISCIPLINE])&&'Table1'[FINISH DATE]>=[mindatefinish]),[DOCUMENT NUMBER])
    )

2. Result:

If I have misunderstood your meaning, you can express the expected result in the form of pictures, we can help you better

Best Regards

Liu Yang

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

[Mezga]

Hi @v-yangliu-msft ,

I tried your solution, and it definitely does work in principle, however I can only select one option from the slicer. Selecting two or more, or having none selected (i.e. show all), creates a blank table.

Is there a way to allow any number of selections from the slicer?

[Mezga]

Bump...is anyone able to assist with this problem?

[Mezga]

Thank you @v-yangliu-msft , I'll give that a shot and report back.

[amitchandak]

@Mezga , Try a measure like

measure =
var _1 = summarize(filter(generate(Table1, Table2), Table2[Date] >= Table1[Start Date] && Table2[Date] <= Table1[Finish Date]), "_1",[STARTED DOCUMENTS])
return
sumx(_1,[STARTED DOCUMENTS])

[Mezga]

Thanks for your response @amitchandak 

I'm trying to filter Table 2 based on Table 1 fields, e.g. the discpline of the document - "Electrical" for instance. Not sure if your measure will achieve that, unless there's something I'm missing.

Table 2 still needs to have all dates from start to finish.

How would I get the calculated totals in Table 2 to only look at a specific discipline type, and ignore the rest?