Filter multiple columns and use a list of values

[Alexxci]

Hi,

I'm currently facing and issue that I cannot resolve and I hope that someone can bring some light to the problem:

I have the following dataset where each Date matches a code, for both dates (Start and End share the same code): 

The requirement is to select a code (Which includes the start and end codes) and return the total count of active parts before the date associated for each description (Active part means that it does not have an end date prior to the associated code date).
Example -> If the user selects the code "C2" I need to return all the active parts for each description prior to the related date for the code selected which is 17/02/2021:
Description A: 3
Description B: 3
Description C: 1

Kind Regards,
Alex

[v-yangliu-msft]

Hi  @Alexxci ,

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
var _1=SUMMARIZE('Table',[Part],[Description],[StartDate],[EndDate],[Code Start])
var _2=SUMMARIZE('Table',[Part],[Description],[StartDate],[EndDate],[Code End])
return
UNION(_1,_2)

2. Create measure.

Measure =
var _selected=MAX('Table 2'[StartDate])
return
IF(MAX('Table'[Column])<=_selected,1,0)

Measure2 =
var _table=SUMMARIZE('Table',[Part],[Description],"1",[Measure])
return
SUMX(_table,[1])

Measure2 =
var _table=SUMMARIZE('Table',[Part],[Description],"1",[Measure])
return
SUMX(_table,[1])

3. Put [Code Start] in Table2 into the slicer

4. Place [Flag] in the Filter and set is = 1.

5. Result:

I still don’t know the result of this B. According to logic, these five are all possible.

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

[Alexxci]

Thanks for your help!

It's close but the key part is that the Result always depends based on which Date the user selects.
So if the user select 17/02/2021 it has to show all the possible values that have a Start Date prior to this date (Not included) that doesn't contain an End Date prior to this date

[Greg_Deckler]

@Alexxci Why is B 3?

[Alexxci]

Actually I made a mistake, the total count should be:
Description A = 3
Description B = 3
Description C = 1

The total count of parts for the Description B that matches the requirement
When the user selects the code C2 it has 3 distinct Description values that contains either in the Code Start or Code End column this code. Then We need to count how many rows have a Start date prior to 17/02/2021 and doesn't have an End Date also prior to 17/02/2021.