Forum Discussion

lukinfo's avatar
lukinfo
Frequent Visitor
2 years ago
Solved

DAX - difference olny in one column

Difference = var _pre=SUMX(FILTER(ALLSELECTED('Table'),[week]=MAX('Table'[week])-1&&[day]=MAX('Table'[day])),[sum])
var _diff=IF(ISBLANK(_pre),BLANK(),[sum]-_pre)
return _diff

Is it possible to change the DAX code to display the difference only in column 36?

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi lukinfo ,

    Based on my testing again, it is impossible to display sum from columns 35 and 36.

    Please try the following methods as workaround:

    1.Create the measure to calculate.

     

    Difference = 
VAR pre = SUMX(FILTER(ALLSELECTED('Table'),[week] = MAX('Table'[week])-1 && [day] = MAX('Table'[day])),[su])
VAR SELE = MAX('Table'[week])
RETURN
IF(SELE = 36, [su] - pre, BLANK())

     

    2.Drag the measure into the table visual.

    3.The result is shown below. The difference and per can’t be invisible. Because the difference is measure.

    4.Power BI does not support this feature currently. You can also raise a new idea and add the comments there to make the drill through feature that maintain the previously selected row coming sooner.

    Home (microsoft.com)

     

    Best Regards,

    Wisdom Wu

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

     

     

     

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi lukinfo ,

    Based on my testing, if drag the sum measure into the values, the matrix don’t display the only column.

     

    Because the difference based on the sum measure, if set the week filter is 36, the difference is incorrect.

    Please try the following methods and check if they can solve your problem:

    1.Create the measure to calculate.

     

    Difference = 
VAR pre = SUMX(FILTER(ALLSELECTED('Table'),[week] = MAX('Table'[week])-1 && [day] = MAX('Table'[day])),[su])
VAR SELE = MAX('Table'[week])
RETURN
IF(SELE = 36, [su] - pre, BLANK())
    Perc = 
VAR pre = SUMX(FILTER(ALLSELECTED('Table'),[week] = MAX('Table'[week])-1 && [day] = MAX('Table'[day])),[su])
VAR SELE = MAX('Table'[week])
RETURN
IF(SELE = 36, DIVIDE([su],pre), BLANK())

     

    2.Drag the measure into the table visual. Delete the sum measure from the values.

    3.The result is shown below.

     

    Best Regards,

    Wisdom Wu

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

     

    • lukinfo's avatar
      lukinfo
      Frequent Visitor
      2 years ago

      Thank you very much for your reply. Unfortunately, this does not solve my problem because I also need to display the sum from columns 35 and 36. Exactly as below:

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi lukinfo ,

        Based on my testing again, it is impossible to display sum from columns 35 and 36.

        Please try the following methods as workaround:

        1.Create the measure to calculate.

         

        Difference = 
VAR pre = SUMX(FILTER(ALLSELECTED('Table'),[week] = MAX('Table'[week])-1 && [day] = MAX('Table'[day])),[su])
VAR SELE = MAX('Table'[week])
RETURN
IF(SELE = 36, [su] - pre, BLANK())

         

        2.Drag the measure into the table visual.

        3.The result is shown below. The difference and per can’t be invisible. Because the difference is measure.

        4.Power BI does not support this feature currently. You can also raise a new idea and add the comments there to make the drill through feature that maintain the previously selected row coming sooner.

        Home (microsoft.com)

         

        Best Regards,

        Wisdom Wu

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