Forum Discussion

Mezga's avatar
Mezga
Helper I
4 years ago

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, ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
Mezga
Helper I
4 years ago

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's avatar
    Mezga
    Helper I
    4 years ago

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