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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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