cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Need help on calculated column which relates to multiple rows

Hi all,

 

I am relatively new to Power BI and DAX and I am having some issue with the following data. I would like to find out which relation numbers have ended in the previous year. However, some relation number have received a new license number so they should not be included. 

To illustrate, relation number 1829 had a license number that ended in 31-dec-2017, but they received a new license number. 

The relation number 1444 had a license number that ended in 31-dec-2017 and did not receive a new license number. I would like to filter on those relation number that had their license number ended and did not receive a new one. 

 

License numberRelation numberStarting dateEnding date
9809182901-jan-1031-dec-17
11907182901-jan-1831-dec-30
10023250901-jan-1231-dec-30
8338144401-jan-0831-dec-17
15222389801-jan-1531-dec-32
594498301-jan-0631-dec-15
1488898301-jan-1631-dec-28

 

This is the preferred output:

License numberRelation numberStarting dateEnding dateBroken relation
9809182901-jan-1031-dec-170
11907182901-jan-1831-dec-300
10023250901-jan-1231-dec-300
8338144401-jan-0831-dec-171
15222389801-jan-1531-dec-320
594498301-jan-0631-dec-150
1488898301-jan-1631-dec-280

 

Thanks in advance.

 

Steef

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Need help on calculated column which relates to multiple rows

@Anonymous Please try this as a "New Column"

 

BrokenFlag = IF(CALCULATE(COUNTROWS(Test157),FILTER(ALL(Test157),Test157[RelationNumber]=EARLIER(Test157[RelationNumber]) && Test157[EndDate] > TODAY()))>0,0,1)

image.png



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





View solution in original post

4 REPLIES 4
Highlighted
Anonymous
Not applicable

IF statement on related rows

Hi all,

 

I am relatively new to Power BI and DAX and I am having some issue with the following data. I would like to find out which relation numbers have ended in the previous year. However, some relation number have received a new license number so they should not be included. 

To illustrate, relation number 1829 had a license number that ended in 31-dec-2017, but they received a new license number. 

The relation number 1444 had a license number that ended in 31-dec-2017 and did not receive a new license number. I would like to filter on those relation number that had their license number ended and did not receive a new one. 

 

License numberRelation numberStarting dateEnding date
9809182901-jan-1031-dec-17
11907182901-jan-1831-dec-30
10023250901-jan-1231-dec-30
8338144401-jan-0831-dec-17
15222389801-jan-1531-dec-32
594498301-jan-0631-dec-15
1488898301-jan-1631-dec-28

 

This is the preferred output:

License numberRelation numberStarting dateEnding dateBroken relation
9809182901-jan-1031-dec-170
11907182901-jan-1831-dec-300
10023250901-jan-1231-dec-300
8338144401-jan-0831-dec-171
15222389801-jan-1531-dec-320
594498301-jan-0631-dec-150
1488898301-jan-1631-dec-280

 

Thanks in advance.

 

Steef

Super User
Super User

Re: Need help on calculated column which relates to multiple rows

@Anonymous Please try this as a "New Column"

 

BrokenFlag = IF(CALCULATE(COUNTROWS(Test157),FILTER(ALL(Test157),Test157[RelationNumber]=EARLIER(Test157[RelationNumber]) && Test157[EndDate] > TODAY()))>0,0,1)

image.png



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





View solution in original post

Super User
Super User

Re: IF statement on related rows

@Anonymous Please refer the solution in the below post 

 

https://community.powerbi.com/t5/Desktop/Need-help-on-calculated-column-which-relates-to-multiple-rows/m-p/575490#



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





v-jiascu-msft Super Contributor
Super Contributor

Re: Need help on calculated column which relates to multiple rows

Hi @Anonymous,

 

Could you please mark the proper answers as solutions?

 

Best Regards,
Dale

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 110 members 1,064 guests
Please welcome our newest community members: