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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Newbie_BiPower
New Member

DAX to check if all child IDs have a specific value

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,

3 REPLIES 3
v-linyulu-msft
Community Support
Community Support

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:

vlinyulumsft_0-1713773009646.png

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.

vlinyulumsft_1-1713773228017.png

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.

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:

IDChild IDReference IDRelease StatusType
15 Don’t CareFeature
25,6 Don’t CareFeature
35,7 Don’t CareFeature
45,6,7,8 Don’t CareFeature
5 9Don’t CareFunction
6 9, 10Don’t CareFunction
7 10, 11Don’t CareFunction
8 12Don’t CareFunction
9  YesComment
10  YesComment
11  NoComment
12  NoComment

 

Expected Output:

IDExpected_Output
1TRUE
2TRUE
3FALSE
4FALSE

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.

vlinyulumsft_0-1715137559744.png

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors