Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mezga
Helper I
Helper I

Filtering 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 NUMBERSTART DATEFINISH DATEDISCIPLINE
DOC-101/01/2203/01/22MECHANICAL
DOC-202/01/2204/01/22STRUCTURAL
DOC-302/01/2205/01/22ELECTRICAL
DOC-402/01/2204/01/22ELECTRICAL

 

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/2210
02/01/2240
03/01/2241
04/01/2243
05/01/2244
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 6
v-yangliu-msft
Community Support
Community Support

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:

vyangliumsft_0-1646271336499.png

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

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?

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

amitchandak
Super User
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])

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.