Forum Discussion
Helper IFiltering between unrelated tables
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?
6 Replies
- amitchandak
Super User
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])
MezgaThanks 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?
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 _mindatestartmindatefinish = var _select=SELECTEDVALUE('Table1'[DISCIPLINE]) var _mindatefinish=CALCULATE(MIN('Table1'[FINISH DATE]),FILTER(ALL(Table1),'Table1'[DISCIPLINE]=_select)) return _mindatefinishSTARTED 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
Thank you Anonymous , I'll give that a shot and report back.
- Mezga
Hi Anonymous ,
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?
