HASONEVALUE alternatives?

[WestWinter]

So I currently have this formula that works well to show a blank value at an aggregate level, while showing up correctly at a row level. However, I noticed that if the aggregate level only has one value (that is the row value), it still shows the row value... Is there an alternative to this, where it shows no values at aggregate level at all?

Label =

// Blank at aggregate level
if(HASONEVALUE('Table'[Code]),
LASTNONBLANK('Table'[Code],1),
BLANK()
)

[v-xiaotang · Accepted solution]

Hi @WestWinter 

you can try like this,

turn off Row subtotals

Best Regards,

Community Support Team _Tang

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

[WestWinter]

bump

[mahoneypat]

Have you tried an expression like this?

Label =

// Blank at aggregate level
if(ISINSCOPE('Table'[Code]),
LASTNONBLANK('Table'[Code],1),
BLANK()
)

Pat

[amitchandak]

@WestWinter , can you shared the expected output example, Isfiltered or  isinscope can help

example

if(isinscope ('Table'[Code]),
LASTNONBLANK('Table'[Code],1),
BLANK()
)

[WestWinter]

@amitchandak This is what I get using my original formula:

As you can see in my matrix, the row with only one value under it will replicate the value in the 2nd column. However, if there are 2 or more values under the row, it will not replicate the value in the 2nd column.

I had tried your formula, but everything is just blanked out in a non-filtered state.

[amitchandak]

@WestWinter , Not clear with your example. 

Can you share the expected output and data source (some sample data). 

Seem like you need to check if the below level has only one row. In that can we need create a formula with allexcept  

[WestWinter]

@amitchandak This is an example of I have on my matrix by using HASONEVALUE formula above if there is only 1 row of value for a particular section (in this case, USA for North America/America).

Require the rows (Americas and North America) to be blank like what is happening for Asia & North Asia.

Thanks!

Countries	Country Code
Americas	USA
North America	USA
United States of America	USA
Asia
North Asia
China	CN
Japan	JP
Korea	KR