Forum Discussion

RubenIdx's avatar
RubenIdx
Helper I
6 years ago
Solved

Optimizing Hasonevalue()

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 Ha...
General Comment
Interesting Links
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
RubenIdx
Helper I
6 years ago

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.