Forum Discussion

itsranga's avatar
itsranga
Helper I
1 year ago
Solved

Need Help - DAX Code

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 resu...
Need Help
  • FBergamaschi's avatar
    FBergamaschi
    1 year ago

    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

itsranga's avatar
itsranga
Helper I
1 year ago

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's avatar
FBergamaschi
Super User
1 year ago

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's avatar
    itsranga
    Helper I
    1 year ago
    Measure =
VAR CurrentID = SELECTEDVALUE ( Table[Ticketno] )
RETURN
CALCULATE (
    SUM ( Table[Hours] ),
    Table[Parent Ticket] = CurrentID
)

    FBergamaschi i tried using this measure

    • FBergamaschi's avatar
      FBergamaschi
      Super User
      1 year ago

      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