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
Peter_2020
Helper III
Helper III

Specific column

Hi all, 

 

I would like to ask you for your help. I have one table where I have following data:

Peter_2020_1-1668627060805.png

And what I need to do is use the filter for "OPERATION = "0090" AND "CODE"= "20" to get proper list of the PO and proper hours for specific PO. But when I use the filters I receive following data:

 

Peter_2020_2-1668627265834.png

So in hours column is 0.00. Desired output should be 35.22. So I need to define some measure which calculate all hours for specific PO with 01 CODE and send it to row with operation 0090 and CODE 20...

 

Any idea how to do it?

Thank you in advance for your help. 

P. 

 

1 ACCEPTED SOLUTION

Hi @Peter_2020 ,

 

Sorry, I misunderstood before. If you are expecting it to show the sum of the hours for a specific PO, please try:

Measure = CALCULATE(SUM('Table'[HOURS]),ALLEXCEPT('Table','Table'[PO]))

Final output:

vjianbolimsft_0-1668669512900.png

Best Regards,

Jianbo Li

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

View solution in original post

7 REPLIES 7
v-jianboli-msft
Community Support
Community Support

Hi @Peter_2020 ,

 

Please try:

Measure = 
var _a = SUMX(ALL('Table'[HOURS]),[HOURS])
var _b = SUM('Table'[HOURS])
return IF(ISINSCOPE('Table'[CODE]),_b,_a)

Output:

vjianbolimsft_0-1668666740122.png

 

Best Regards,

Jianbo Li

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

Hi @v-jianboli-msft , 

thanks for your reply. It works perfect when you have table with 1 PO. But in my case I have in the table around 1000 PO´s and then it return the sum for all hours in total:

Peter_2020_0-1668668536574.png

So the SUM of the hours must be defined for specific PO and must be in the same row, not in total.

P. 

Hi @Peter_2020 ,

 

Sorry, I misunderstood before. If you are expecting it to show the sum of the hours for a specific PO, please try:

Measure = CALCULATE(SUM('Table'[HOURS]),ALLEXCEPT('Table','Table'[PO]))

Final output:

vjianbolimsft_0-1668669512900.png

Best Regards,

Jianbo Li

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

Hi @v-jianboli-msft , 

one more request:

is it possible to adjust the formula to get proper sum of the hours with operation filter?:

Peter_2020_0-1671455726473.png

it means that when I set filter for operation 0050 I get just the sum of the hours for specific operation - in this case 3,6 hours, for operation 0070 - 2,58; 0080 - 6,9 and so on....

so not the sum of all hours but just for specific operation...

Thank you in advance. 

P. 

Hi @v-jianboli-msft , 

excellent! That's exactly what I needed. Thank you a lot for your help!

AllisonKennedy
Super User
Super User

@Peter_2020 Why are you wanting to recode these values? You could use 'Replace Values' in Power Query, or duplicate the column first and then 'Replace Values' in power query, but we need to know more info on why to be able to provide the best answer for you.


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Hi @AllisonKennedy , 

 

because I need to know SUM of the hours for each specific PO´s that contains OPERATION "0090" and CODE "20". There are also PO´s that don´t have OPERATION "0090" and CODE "20" therefore I need to use this filter and somehow get SUM of the hours. 

P. 

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.