Filter by all rows of a project

[Aikeno]

Hello everyone,

An illustration of my tables: Yellow and orange are link by the "Project_ID"

My goal is to create a table in my report (the blue one) with a column "Test scheduled"=Yes, if all the activites have a "Test start" fullfilled.

In this case Project 1 in my blue table will be No in "Test scheduled" because A-003 which is link to P-001 has no "Test start" even if the A-001 have a fullfilled one.

Actually I have two line for Project 1:

One with "Test scheduled" yes and another one with no.

Thanks in advance for your help ! 

[Anonymous]

Hi @Aikeno ,

If I understand correctly, the issue is that you want to filter by all rows of a project. Please try the following methods and check if they can solve your problem:

1.Create the simple YTable and OranTable. 

2.Manage relationship between Ytable and OranTable by the Project_ID.

3.Create the new measure to filter. Enter the following DAX formula.

Test scheduled = var _yproject=SELECTEDVALUE('YTable'[Project_ID])
var _count=CALCULATE(COUNT('OranTable'[Project_ID]), FILTER(ALLSELECTED('OranTable'),'OranTable'[Project_ID]=_yproject))
var _tscount=CALCULATE(COUNT('OranTable'[Project_ID]), FILTER(ALLSELECTED('OranTable'),'OranTable'[Project_ID]=_yproject&&NOT(ISBLANK('OranTable'[Test start]))))
return if(ISBLANK(_count),BLANK(),if(_count=_tscount,"Yes","No"))

4.Drag the Test scheduled to the Ytable visual.

5.The result is shown below.

Best Regards,

Wisdom Wu

[Aikeno]

Thanks a lot it works fine !

Just a little problem when a Project have no associated activities I can't see it in my table, would it be possible ?

And to be perfect I have another request, if in my orange table I had a column with activity state would I be able to display "Yes" for "Test scheduled" only for some of them ?

Draft/proposed would be "No"

Planning/Active/Report/Review/Closed would be "Ok"

Exemple:

it would be really helpfull it something like this could work.

Thanks a lot in advance for your time and help !

[Anonymous · Accepted solution]

Hi @Aikeno ,

Please try the following methods and check if they can solve your problem:

1.Create the Table Orange status.

2.Create the new measure.

MeasureTestscheduledStatus = 
var _yproject = SELECTEDVALUE('YTable'[Project_ID])
var _count = CALCULATE(COUNT('Table Orange status'[Project_ID]), FILTER(ALLSELECTED('Table Orange status'),'Table Orange status'[Project_ID]=_yproject))
var _status = SELECTEDVALUE('Table Orange status'[Activity_status])
var _tscount = CALCULATE(COUNT('Table Orange status'[Project_ID]), FILTER(ALLSELECTED('Table Orange status'),'Table Orange status'[Project_ID] = _yproject&&'Table Orange status'[Activity_status] IN {"Planning","Active"} && NOT(ISBLANK('Table Orange status'[Test start]))))

return if(ISBLANK(_count),BLANK(),if(_count = _tscount ,"Yes","No"))

3.Drag the measure into the table visual.

4.The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Aikeno , Create a measure like

Test Scheduled=

var _1 = Max(Filter(Table, not(Isblank(Table[Test Date])))

return

if(_1 <=today(), "No", "Yes")

[Aikeno]

Hello,

Thank you for your help but im not able to replicate it.