Forum Discussion

Peter_Ronaldon's avatar
Peter_Ronaldon
Frequent Visitor
2 years ago
Solved

PowerBI Measure Adjustment

Hiiii!

 

I have the following DAX Measure in BI:

 

Price Impact =
VAR LY_Sales = [Sales PY]
VAR CY_Sales = [Sales CY]
VAR LY_Quantity = [Quantity PY]
VAR CY_Quantity = [Quantity CY]
VAR LY_Price_Per_Unit = [LY_Price_Per_Unit]
VAR CY_Price_Per_Unit = [CY_Price_Per_Unit]

RETURN
IF(
ISBLANK(CY_Quantity),
0,
IF(
ISBLANK(LY_Sales) && ISBLANK(CY_Sales),
0,
IF(
ISBLANK(LY_Sales) || LY_Quantity = 0,
IF(
NOT ISBLANK(CY_Price_Per_Unit),
CY_Price_Per_Unit * LY_Quantity,
0
),
IF(
ISBLANK(CY_Sales) || CY_Quantity = 0,
IF(
NOT ISBLANK(LY_Price_Per_Unit),
LY_Price_Per_Unit * LY_Quantity,
0
),
(CY_Price_Per_Unit - LY_Price_Per_Unit) * LY_Quantity
)
)
)
)

 

 

 

When the DAX measure is not applied to my visual it appears as:

 

 

However, when I apply the Price Impact measure to my report, I have thousands of blank lines appear (blank as in, customer name, P CODE, item number etc is correct and showing, however, there is no quantity or price for PY or CY - Is there a way that I can stop all of these blank rows showing up when I add this Price Impact Measure?

 

For instance, there are no sales (PY or CY) and no Quantity (PY or CY), however, a line is still added with this Price Impact Measure returning a value of $0. Is there a way to stop this from happening without simply filtering out blank rows in say column PY Quantity on the visual itself?

 

 

Thank you! 

 

 

 

 

 

Need Help
  • amitchandak's avatar
    amitchandak
    2 years ago

    Peter_Ronaldon , do not set value to 0. Set them to blank

     

    ETURN
    IF(
    ISBLANK(CY_Quantity),
    Blank() ,
    IF(
    ISBLANK(LY_Sales) && ISBLANK(CY_Sales),
    Blank() ,
    IF(
    ISBLANK(LY_Sales) || LY_Quantity = 0,
    IF(
    NOT ISBLANK(CY_Price_Per_Unit),
    CY_Price_Per_Unit * LY_Quantity,
    Blank()
    ),
    IF(
    ISBLANK(CY_Sales) || CY_Quantity = 0,
    IF(
    NOT ISBLANK(LY_Price_Per_Unit),
    LY_Price_Per_Unit * LY_Quantity,
    Blank()
    ),
    (CY_Price_Per_Unit - LY_Price_Per_Unit) * LY_Quantity
    )
    )
    )

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    Peter_Ronaldon , do not set value to 0. Set them to blank

     

    ETURN
    IF(
    ISBLANK(CY_Quantity),
    Blank() ,
    IF(
    ISBLANK(LY_Sales) && ISBLANK(CY_Sales),
    Blank() ,
    IF(
    ISBLANK(LY_Sales) || LY_Quantity = 0,
    IF(
    NOT ISBLANK(CY_Price_Per_Unit),
    CY_Price_Per_Unit * LY_Quantity,
    Blank()
    ),
    IF(
    ISBLANK(CY_Sales) || CY_Quantity = 0,
    IF(
    NOT ISBLANK(LY_Price_Per_Unit),
    LY_Price_Per_Unit * LY_Quantity,
    Blank()
    ),
    (CY_Price_Per_Unit - LY_Price_Per_Unit) * LY_Quantity
    )
    )
    )