Forum Discussion

cristianml's avatar
cristianml
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

HASONEVALUE CALCULATE COLUMN SUMX

Hi,

I need help with the following measure.

 

 

NWD_2 =
VAR REV = 'Revenue - CCI'[Rev Current Quarter]
VAR NWD = 'Revenue - CCI'[NWD Value]
VAR NWD_2 = IF(REV=BLANK() && NWD>0,BLANK(),NWD)
RETURN
IF(HASONEVALUE('00_CALENDAR'[FY & Q]),NWD_2,SUMX(VALUES('00_CALENDAR'[FY & Q]),NWD_2))

 

I need to the result of 36.2 (sum of 16.9 + 19.3)  instead of what is given in the red circle.     

Not sure why HASONEVALUE is not working in this case.

 

Coudl you help me to correct the issue ?

 

Thanks !

 

data protection
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  cristianml ,

    You can try to modify dax as follows:

    NWD_2 =
IF(
HASONEVALUE('00_CALENDAR'[FY & Q]),
Sumx(
Values('00_CALENDAR'[FY & Q]),
IF([Rev Current Quarter]>0 && [NWD Value]>0, [NWD Value],blank()),
Blank())

    Or You can create a measure.

    Sum_Total =
VAR _table =
    SUMMARIZE ( '00_CALENDAR', '00_CALENDAR'[FY & Q], "_value", [ NWD_2] )
RETURN
    IF ( HASONEVALUE ( '00_CALENDAR'[FY & Q] ), [NWD_2], SUMX ( _table, [_value] )

     

    Best Regards,

    Liu Yang

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

4 Replies

    • powerbi_zone's avatar
      powerbi_zone
      Frequent Visitor
      4 years ago

      Hello littlemojopuppy ,

      The HASONEVALUE is a very simple dax which returns TRUE when the context for column Name has been filtered to one distinct value only. Otherwise is FALSE.

      Syntax: HASONEVALUE(<ColumnName>)
      Parameter: It passes the name of an existing column. It cannot be an expression.

      Return value : Boolean TRUE/FALSE

      You can get the sample data and pbix from the below video in the description/comment section

      Hope this helps!

      Usage of Hasonevalue DAX Function  

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  cristianml ,

    You can try to modify dax as follows:

    NWD_2 =
IF(
HASONEVALUE('00_CALENDAR'[FY & Q]),
Sumx(
Values('00_CALENDAR'[FY & Q]),
IF([Rev Current Quarter]>0 && [NWD Value]>0, [NWD Value],blank()),
Blank())

    Or You can create a measure.

    Sum_Total =
VAR _table =
    SUMMARIZE ( '00_CALENDAR', '00_CALENDAR'[FY & Q], "_value", [ NWD_2] )
RETURN
    IF ( HASONEVALUE ( '00_CALENDAR'[FY & Q] ), [NWD_2], SUMX ( _table, [_value] )

     

    Best Regards,

    Liu Yang

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