HASONEVALUE CALCULATE COLUMN SUMX

[cristianml]

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 !

[v-yangliu-msft · Accepted solution]

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.

[littlemojopuppy]

@cristianml can you provide some sample data to work with?

[powerbi_zone]

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  

[littlemojopuppy]

@powerbi_zone I'm not sure why this is directed to me, but it certainly seems like a commercial for something...