DAX to check if all child IDs have a specific value

[Newbie_BiPower]

Hi,

I'm new to powerBi and not sure how to go about do the below.

I have a table with the below columns,

• ID - has unique numeric values
• Child ID - could be either blank, or could have one of the IDs, or can contiain multiple IDs separated by a comma
• Release Status - Has Yes, no, blank
• Type - Has Comment, Function as the field values

What I want to do is for each ID, I want to look at all the referenced Child ID, and if the child ID is Function then I wan to check the release status. 

For each ID i want to determine if all of the child function ID are in release status "Yes". 

Thanks,

[v-linyulu-msft]

Hi, @Newbie_BiPower 

First of all, as I understand it, your requirement is to search by id, if the sub-id exists and the "type" is function and the "release status" is "YES", then if the If the whole group is satisfied, then it is true, otherwise it is false. if my understanding is wrong, please give me your relative suggestion.

1.Regarding the issue you raised, my solution is as follows:

First of all I have created only one table which is as follows:

2.Next, I created a measure that uses the countrows function to group columns whose sub-data is not null and whose "Type" is "function" or "release status." The "YES" function grouping of columns whose "Type" is "Function" or "Release Status" is compared and returns true, otherwise it is false. My Dax function is as follows:

Measure = 
VAR FUCNCTION=CALCULATE(COUNTROWS('T&F'),FILTER(ALLSELECTED('T&F'),'T&F'[id]=MAX('T&F'[id])&&NOT(ISBLANK('T&F'[childid]))&&'T&F'[Type]="Function"))
VAR yes1=CALCULATE(COUNTROWS('T&F'),FILTER(ALLSELECTED('T&F'),'T&F'[id]=MAX('T&F'[id])&&NOT(ISBLANK('T&F'[childid]))&&'T&F'[Release Status]="YES"&&'T&F'[Type]="Function"))
RETURN 
IF(FUCNCTION=yes1,TRUE(),FALSE())

3.Here's my final result, which I hope meets your requirements.

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

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

[Newbie_BiPower]

Hi @v-linyulu-msft 

Thank you for your proposal, i think that worked, but i don't i clearly mentioned needs. As suggested by you i have tried to create a table that shows the input and ouput I'm expecting.

For each feature, find all the linked child IDs, and for each of those child IDs find all the linked reference IDs and see if the release status is Yes. If all linked referenced ID have yes for release status then the output would be True, and if one or more of the release status of the linked reference IDs is No then the output would be False.

Input Table:

ID	Child ID	Reference ID	Release Status	Type
1	5		Don’t Care	Feature
2	5,6		Don’t Care	Feature
3	5,7		Don’t Care	Feature
4	5,6,7,8		Don’t Care	Feature
5		9	Don’t Care	Function
6		9, 10	Don’t Care	Function
7		10, 11	Don’t Care	Function
8		12	Don’t Care	Function
9			Yes	Comment
10			Yes	Comment
11			No	Comment
12			No	Comment

Expected Output:

ID	Expected_Output
1	TRUE
2	TRUE
3	FALSE
4	FALSE

[v-linyulu-msft · Accepted solution]

Hi,@Newbie_BiPower 

Regarding the issue you raised, my solution is as follows:

1.To meet your needs, I have created a new calculated tables and here is my DAX function:

Table = 
VAR _vtable = FILTER(CROSSJOIN(FILTER(SELECTCOLUMNS('t&f',"_ID1",'t&f'[ID],'t&f'[Child id]),'t&f'[Child id]<>BLANK()),FILTER(SELECTCOLUMNS('t&f',"_ID2",'t&f'[ID],'t&f'[Reference ID]),'t&f'[Reference ID]<>BLANK())),FIND([_ID2],[Child ID],,BLANK())<>BLANK())
VAR _vtable2 = FILTER(CROSSJOIN(SUMMARIZE(_vtable,[_ID1],"_outcome",CONCATENATEX(FILTER(_vtable,[_ID1]=EARLIER([_ID1])),[Reference ID])),FILTER(SELECTCOLUMNS('t&f','t&f'[ID], 't&f'[Release Status]),[Release Status]<>"Don’t Care")),FIND([ID],[_outcome],,BLANK())<>BLANK())
RETURN
SELECTCOLUMNS(SUMMARIZE(SELECTCOLUMNS(SUMMARIZE(_vtable2,[_ID1],[Release Status]),[_ID1],"A",CONCATENATEX(FILTER(SUMMARIZE(_vtable2,[_ID1],[Release Status]),[_ID1]=EARLIER([_ID1])),[Release Status])),[_ID1],[A],"Outcome",IF(FIND("No",[A],,BLANK())<>BLANK(),"Flase","True")),[_ID1],[Outcome])

2.Here's my final result, which I hope meets your requirements.

Can you share sample data and sample output in tabular format if I am misunderstanding? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Leroy Lu

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