Optimizing Hasonevalue()

[RubenIdx]

Hi Everybody, I want to know if is it possible to optimize Hasonevalue() in powerpivot I've created a model that runs some slowly and this is one of the principal reasons, I use many verifications Hasonevalue necessarly my fact table is around 200,000 lines and Dax Studio indicates that most of the measures involving hasonevalue() are which consume most of the runing time. 

In this example I want to do a calculation only for subtotal/total row when the table shows Items(products) it has to show blank() value.

Thanks in advance!

Here my code:

MixEffect:= IF (
HASONEVALUE ( Sales[Item] ),
0,
DIVIDE ( Sales[FxMixV], [Last Sale], 0 )
)

[Anonymous · Accepted solution]

HI @RubenIdx,

According to your formula, it seems like you nested other formulas in the current measure. Did you nested an iterator function? Can you please share formulas that you used to help us clarify your scenario?

Optimizing nested iterators in DAX 

In addition, dax functions in excel are different from power bi dax function, it will be help if you share some dummy data to test.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[RubenIdx]

Hi Xiaoxin Sheng!

It was very useful the reference you gave me this one made me investigate more about nested queries in DAX and indeed this significantly improved the performance of my model.

At the beginning I used this measure to calculate the amount of sales and a similar measure to calculate the total units.

Venta Actual:=CALCULATE([ZVBase],
			Periodos[Año]=2020,
			ALLSELECTED(Periodos[Mes])
			)

Performance improved when I applied summarize in these calculations.

Venta Actual:=CALCULATE(
sum(ventas[VBase]),
FILTER(
ADDCOLUMNS(
SUMMARIZE(Ventas,Periodos[Año],Productos[Producto]),
"Ventas Act",Ventas[zventa]
),
Periodos[Año]=IF ( HASONEVALUE ( Selector1[Actual] ), VALUES ( Selector1[Actual] ), 2020 ))
,ALLSELECTED(Periodos[Mes]))

I would be very grateful if you could give me your opinion on the way I proposed my measure.

[PaulDBrown]

@RubenIdx 

Is the function ISINSCOPE on Power Pivot? If so, try using that instead (same syntax).

Please let us know if there is a difference (I'm curious!)

[RubenIdx]

Hi! I have reviewed and this function is not available in powerpivot. Thanks