Need Help - DAX Code

[itsranga]

Hi Team,  I have a Data like this (Top table), I need result like bottom table

• If the Parent Ticket is not blank, the Relevant hrs should be blank.
• If the Parent Ticket is blank, the result should be the sum of hours for all rows that the same Parent Ticket.
  
  how to achieve this using DAX

[v-tejrama]

Hi @itsranga ,

Thanks for sharing the detailed process you’ve explained it quite well, and your approach is absolutely correct. Just to confirm, once you load your data into Power BI from Excel or your relevant source, you can go ahead and create the "Relevant Hrs" calculated column in the Data view.
The DAX expression you've mentioned works fine  it checks if the Parent Ticket is not blank and then uses CALCULATE with ALLEXCEPT to sum the Total Hrs grouped by Parent Ticket. If the Parent Ticket is blank, it rightly returns a blank value. After that, adding the fields like Ticket No, Summary, Parent Ticket, Total Hrs, and the newly created Relevant Hrs into a Table visual in Report view is the right way to go.
Based on your example, if multiple rows share the same Parent Ticket, the Relevant Hrs should show the correct total, like 586 for the Data Conversion case, while entries without a Parent Ticket (like Hourly Conversion) will show blank, which is expected. You can also apply conditional formatting or adjust the column widths to improve readability. Once everything looks good, saving the report and publishing it to the Power BI Service is the final step.
Please feel free to text me back if you have any questions
I’ve attached the .pbix file and screen shorts used in this test for your reference.

Thank you.
Tejaswi.

[itsranga]

@v-tejrama  thanks for your suppot,   somewhat okay  i slightly changed the formula it's okay but 

This is a parent-child ticket concept. For example, one parent ticket can have multiple child tickets. I need to display  in a new column, show the sum of all the child ticket hours.
In this case, ticket number 1043930 is the parent ticket (row 1), and rows 3 and 4 are its child tickets. So, I need to calculate the sum of hours where the ticket number matches the parent ticket, and display that sum in the parent ticket's row

[v-tejrama]

Hi @itsranga ,

Thank you @FBergamaschi  for the helpful input!

Were you able to resolve the issue? If the response addressed your query, kindly confirm. This helps keep the community informed and improves solution visibility.

Thank you for your support!

[FBergamaschi · Accepted solution]

This is very simple but in your original data it was not like this, here is the table you showed

Anyway I adjusted the data and now I have the following

and now I have what you want without any hardwiring:

Test =

VAR Ticket = Tabella[Ticket No]

RETURN

SUMX ( FILTER ( Tabella, Tabella[Parent Ticket] = Ticket ), Tabella[Total Hrs] )

The above is a column

If you want a measure, let me know

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[techies]

Hi @itsranga please try this measure

Relevant Hrs Measure =

VAR ThisTicket = SELECTEDVALUE('Sheet6'[Ticket No])

VAR IsParent = ISBLANK(SELECTEDVALUE('Sheet6'[Parent Ticket]))

RETURN

IF (

    IsParent,

    CALCULATE (

        SUM ( 'Sheet6'[Total Hrs] ),

        FILTER (

            ALL('sheet6'),

            'Sheet6'[Parent Ticket] = ThisTicket

                || 'Sheet6'[Ticket No] = ThisTicket

        )

    )

)

[itsranga]

@techies  thanks for your response,  I need result like this
its return wrong value

[techies]

ok, please try this 

Relevant Child Hrs Measure =

VAR ThisTicket = SELECTEDVALUE('Sheet6'[Ticket No])

RETURN

CALCULATE (

    SUM ( 'Sheet6'[Total Hrs] ),

    FILTER (

        ALL('Sheet6'),

        'Sheet6'[Parent Ticket] = ThisTicket

    )

)

[itsranga]

@techies   No its return wrong value,  some other bigger value it's retun
attached screenshot for your refrence, 

[itsranga]

@FBergamaschi 

Ticket No	Summary	Parent Ticket	Total Hrs
1049039	Data Conversion		678.5
1323146	Table Conversion	10493039	346
1456862	Table Conversion_1	10493039	240
1140978	Hourly Conversion		186.5
I need result like this
Ticket No	Summary	Parent Ticket	Total Hrs	Relevant hrs	Total Hrs
1049039	Data Conversion		678.5	586	1264.5
1323146	Table Conversion	10493039	346
1456862	Table Conversion_1	10493039	240
1140978	Hourly Conversion		186.5

many different parent ticket is not possible, one parent ticket only for one ticket

[FBergamaschi]

calculated column code

Colonna =

IF ( ISBLANK( Tabella[Parent Ticket] ) && Tabella[Summary ] <> "Hourly Conversion", SUMX ( FILTER ( Tabella, NOT ISBLANK( Tabella[Parent Ticket] ) ), Tabella[Total Hrs] ) )

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[itsranga]

@FBergamaschi 

Colonna =

IF ( ISBLANK( Tabella[Parent Ticket] ) && Tabella[Summary ] <> "Hourly Conversion", SUMX ( FILTER ( Tabella, NOT ISBLANK( Tabella[Parent Ticket] ) ), Tabella[Total Hrs] ) )


This IF condition is hardcoded for 'Hourly conversion'. I tried to avoid this code and used a DAX expression instead, but it caused a circular dependency error. Please correct the error

[FBergamaschi]

That was the only way to avoid getting the 586 total also in the last row which also has blank Parent Ticket

I do not find your code, though, where is it? In your message I only see my code

[itsranga]

Measure =
VAR CurrentID = SELECTEDVALUE ( Table[Ticketno] )
RETURN
CALCULATE (
    SUM ( Table[Hours] ),
    Table[Parent Ticket] = CurrentID
)

@FBergamaschi i tried using this measure

[FBergamaschi]

This code cannot work as a measure

Measure =

VAR CurrentID = SELECTEDVALUE ( Tabella[Ticket no] )

RETURN

CALCULATE (

    SUM ( Tabella[Total Hrs] ),

    Tabella[Parent Ticket] = CurrentID

)

My code was for a column. The point is that the last row has an empty Parent Ticket like the first one so how do we make the difference? I wrote 

Colonna =

IF ( ISBLANK( Tabella[Parent Ticket] ) && Tabella[Summary ] <> "Hourly Conversion", SUMX ( FILTER ( Tabella, NOT ISBLANK( Tabella[Parent Ticket] ) ), Tabella[Total Hrs] ) )

but if you prefer you can use 

Colonna =

IF ( ISBLANK( Tabella[Parent Ticket] ) && Tabella[Summary ] = "Data Conversion", SUMX ( FILTER ( Tabella, NOT ISBLANK( Tabella[Parent Ticket] ) ), Tabella[Total Hrs] ) )

and it will work as you were asking? We can avoid the hardwire if you specify another way to identify the last row vs the first one

But now I ask you: do you need a column or a measure? To me, you need a column but of course please let me know otherwise

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[FBergamaschi]

In your picture, in the results table, the first row has empty Parent Ticket, though you want to see 586?

This is in contrast with your definition:

• If the Parent Ticket is blank, the Relevant hrs should be blank.

Please clarify and resend the result you want

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[itsranga]

@FBergamaschi 

• If the Parent Ticket is blank, the result should be the sum of hours for all rows that the same Parent Ticket. (screenshot red color highlighted)
• If the Parent Ticket is not blank, the Relevant hrs should be blank.

[FBergamaschi]

Ok now it is clearer, please provide the table in a usable format, not an image

And What if there are many different Parent Tickets? Like two differen parent tickets in 5 different lines, 3 with the first parent ticket and the other two with the other one?

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI