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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
tomekm
Helper III
Helper III

Label as True/False all the rows belonging to a higher level attribute, based on the sum of values

I would like to create a column formula that will label all the rows for an attribute either as True or False, based on the sum of values/net value resulting from other sub-attributes. Example: task A has a net value of its subtasks as +5, so all the rows belonging to task A would be a TRUE. Task B has a negative value of its sub-tasks as -4, so it would be a FALSE; and so on. The idea is to be able to filter out Tasks with a negative net value: 

 

 

tasksub-taskvalueDesired Output
A1-8TRUE
A217TRUE
A3-4TRUE
B15FALSE
B2-9FALSE
C1-11FALSE
C22FALSE
D10TRUE
D23TRUE

 

Thank you.

2 ACCEPTED SOLUTIONS
TomMartens
Super User
Super User

Hey @tomekm ,

 

the below DAX can be used to create a calculated column:

Column = 
var currentTask = 'SubTasks'[task]
var sumOfSubTasks = 
    SUMX( 
        FILTER( 'SubTasks' , 'SubTasks'[task] = currentTask )
        , 'SubTasks'[value]
    )
return
IF( sumOfSubTasks > 0 , TRUE() , FALSE() )

A screenshot of the table: 
image.png

 

Hopefully, this provides what you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

Ritaf1983
Super User
Super User

Hi @tomekm ,
Try to add column with formula:

test = if(calculate(sum('Table (2)'[value]),ALLEXCEPT('Table (2)','Table (2)'[task]))>0,TRUE(),False).
I have attached a picture for your reference
Ritaf1983_0-1679766758256.png

 

If my answer was helpful please give me a Kudos and accept as a Solution.

View solution in original post

11 REPLIES 11
Ritaf1983
Super User
Super User

Hi @tomekm ,
Try to add column with formula:

test = if(calculate(sum('Table (2)'[value]),ALLEXCEPT('Table (2)','Table (2)'[task]))>0,TRUE(),False).
I have attached a picture for your reference
Ritaf1983_0-1679766758256.png

 

If my answer was helpful please give me a Kudos and accept as a Solution.

Thanks. How can I modify this formula so that it works when a specific filter is applied in the report? For example, there is a "Status" column for the sub-tasks that labels them with values of: current, past, future. I want to be able to filter on sub-tasks with only 1 or 2 specific Statuses, and the TRUE or FALSE formula to perform the calculation based on those filter selections in the report.

Hi,

Write these measures

V = SUM(Data[value])
Measure = CALCULATE([V],ALL(Data[sub-task]))>0

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks Ashish, but it's still not working in the context of filtering. For example, for task A, if sub-tasks 1 and 3 are labelled as 'previous' and sub-task 2 as 'current', and if I then filter on 'previous' only, I'd like to see those 2 entries labelled as FALSE, since they are net negative. And if I filter on 'current' subtasks only, I would want to see sub-task 2 for task A, and labelled as TRUE. Any ideas?

You are welcome.  Share the download link of the PBI file, show the problem and the expected result very clearly.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks for the follow up, Ashish. I no longer need this, as the first solutions posted earlier work for me fine.

Thank you!

TomMartens
Super User
Super User

Hey @tomekm ,

 

the below DAX can be used to create a calculated column:

Column = 
var currentTask = 'SubTasks'[task]
var sumOfSubTasks = 
    SUMX( 
        FILTER( 'SubTasks' , 'SubTasks'[task] = currentTask )
        , 'SubTasks'[value]
    )
return
IF( sumOfSubTasks > 0 , TRUE() , FALSE() )

A screenshot of the table: 
image.png

 

Hopefully, this provides what you are looking for.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Thanks. How can I modify this formula so that it works when a specific filter is applied in the report? For example, there is a "Status" column for the sub-tasks that labels them with values of: current, past, future. I want to be able to filter on sub-tasks with only 1 or 2 specific Statuses, and the TRUE or FALSE formula to perform the calculation based on those filter selections in the report.

Hey @tomekm ,

 

instead of a calculated column you need a measure, as a measure adapts to user interactions.

But you have to be aware that it's not possible to use a measure inside a slicer.
I suggest that you create a measure that returns 1 for ge 0 and 0 for lt 0.

You can use the measure in combination with a visual level filter.

 

Hopefully, this provides an idea of how to tackle your challenge.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Thank you!

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.