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|
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:
AS OF DATE
AS OF DATE
|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.
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]) )
If I have misunderstood your meaning, you can express the expected result in the form of pictures, we can help you better
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
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?
Bump...is anyone able to assist with this problem?
@Mezga , Try a measure like
var _1 = summarize(filter(generate(Table1, Table2), Table2[Date] >= Table1[Start Date] && Table2[Date] <= Table1[Finish Date]), "_1",[STARTED DOCUMENTS])
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?
Find out more about the May 2023 update.
Share your Data Story with the Community in the Data Stories Gallery.