Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

How to display the expected result

Hi All Experts,
I have an issue with my measure, i am not able to get expected results.


measure 1
var v1 = IF( FIRSTNONBLANK('Item'[ITEM_TYPE], 'Item'[ITEM_TYPE]) IN {"FG-1","FG-2","FG-3","04-FG","FG-05","FG-07","FG-08"},"Y","N")

var v2 = IF([Last 12 months v5-Sales]<>0,"Y",v1)
return v2

[Last 12 months v5-Sales] - this is a measure, in this measure we are calculating sum of sales for last 12 months from the user picked date.
User picked date means ex: if user picks year- 2022 and month - oct, then sum of sales will be calculated from oct 2022 to oct 2021.

This is my measure, based on the above measure, it need to display "y", where we have the "[Last 12 months v5-Sales]<>0" and where item_type is any of these ("FG-1","FG-2","FG-3","04-FG","FG-05","FG-07","FG-08"). But with above measure, you can see, i am getting result as "Y", only when this ("[Last 12 months v5-Sales]<>0") is satisfied. it is not considering the second condition the item_type. I want to display the result as "Y", where anyone of the condtion is satisfied.

Need Help

5 Replies

  • SergioSilvaPT's avatar
    SergioSilvaPT
    Resolver V
    3 years ago

    Hi Anonymous ,

     

    Don't know if this is what you need but here is someting similar with a PBI exmple in attach.

     

    If profit > 3.000.000 and country is France or Germany shows  "Y" else shows "N".

    Regards,
    Sérgio Silva

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

    Check out my blog for tips and tricks about Power BI: https://pbibits.wordpress.com/

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Sérgio Silva,
      I have gone through your attachement, it didn't work for me .
      i need to provide all these item_typ = ("FG-1","FG-2","FG-3","04-FG","FG-05","FG-07","FG-08") and this "[Last 12 months v5-Sales]<>0".


      1. If this "[Last 12 months v5-Sales]" is having value then it should be "Y".
      2. If the item_type is anyone from above and if the "[Last 12 months v5-Sales]" value is blank for it then also it should display "Y".

      • SergioSilvaPT's avatar
        SergioSilvaPT
        Resolver V
        3 years ago

        Anonymous i don't know the name of your columns but this should work, just replace the

        table[Category] with the table and column from your category.

         

        Example:

         

        Metric Name = 
IF(
    [Last 12 months v5-Sales] <> 0,
    "Y",
    IF(
        ISBLANK([Last 12 months v5-Sales]) &&
            SELECTEDVALUE(table[Category]) ="FG" ||
            SELECTEDVALUE(table[Category]) = "FGS" ||
            SELECTEDVALUE(table[Category]) = "FGD-SKU" ||
            SELECTEDVALUE(table[Category]) = "04-FG" ||
            SELECTEDVALUE(table[Category]) = "FG-PARENT" ||
            SELECTEDVALUE(table[Category]) = "TANDEM FG" ||
            SELECTEDVALUE(table[Category]) = "FG-FG",
        "Y",
        "N"
    )
)