Forum Discussion

cosminc's avatar
cosminc
Icon for Post Partisan rankPost Partisan
7 years ago
Solved

Measure with if with filtered value

Hi, I try to create a measure based on a filter value and an if something like this: if(source(salesmen) slicer="George",4, if(source(salesmen) slicer ="Mary",0)) can you help me please with the ...
Need Help
  • Stachu's avatar
    Stachu
    7 years ago

    you need SELECTEDVALUE, I'd also consider wheter you want to use BLANK - calculation wise it acts as 0, display wise it acts as null

    IF(
    SELECTEDVALUE(Source[Salesman]) = "George",
    4,
    IF(
        SELECTEDVALUE(Source[Salesman]) = "Mary",
        0,
        BLANK()
    )
)
Stachu's avatar
Stachu
Icon for Community Champion rankCommunity Champion
7 years ago

you need SELECTEDVALUE, I'd also consider wheter you want to use BLANK - calculation wise it acts as 0, display wise it acts as null

IF(
    SELECTEDVALUE(Source[Salesman]) = "George",
    4,
    IF(
        SELECTEDVALUE(Source[Salesman]) = "Mary",
        0,
        BLANK()
    )
)
  • cosminc's avatar
    cosminc
    Icon for Post Partisan rankPost Partisan
    7 years ago

    Hi

    It works fine!

    Thanks,

    Cosmin

  • cosminc's avatar
    cosminc
    Icon for Post Partisan rankPost Partisan
    7 years ago

    Hi

    I have a similar situation but it doesn't works

    In a base i have column Year and Fiscal Year (Year<9,Year,Year+1)

    and now i need to make a column which is Year or Fiscal year and use it instead of simple Year column on grpahs, slicers etc

    in addition with this i made a table in query editor with 2 values and a slicer with them in report visualization

    and i need when i filter one value the Year switchable column to be Year, or i select the other value and will be Year Fiscal

    but it does't work, the calculated column is blank

    Year = IF(
    SELECTEDVALUE(Year_Type[Year_Type]) = "Calendar",
    'Source'[YearCalendar],
    IF(
    SELECTEDVALUE(Year_Type[Year_Type]) = "Fiscal",
    'Source'[YearFiscal],
    BLANK()
    )
    )
     
    any solution?
    Thanks!
    Cosmin

     

     

  • fredzegers's avatar
    fredzegers
    New Member
    4 years ago

    Hi guys,

     

    I do have a similar issue. This quick measure does not work. Does anyone have an idea?

     

    Diff in % van Revenue Gross =
    VAR __BASELINE_VALUE =
        CALCULATE(
            SUM('DWH'[Revenue Gross]),
                'DWH'[Delivery Date.Datum].[Jaar] IN {
    IF(
    SELECTEDVALUE(DWH[Delivery Date.Jaar]) = 2022,2021,
    IF(
    SELECTEDVALUE(DWH[Delivery Date.Jaar]) = 2021,2020,
    IF(
    SELECTEDVALUE(DWH[Delivery Date.Jaar]) = 2020,2019,
    BLANK()
    )
    ))}
        )
    VAR __MEASURE_VALUE = SUM('DWH'[Revenue Gross])
    RETURN
        IF(
            NOT ISBLANK(__MEASURE_VALUE),
            DIVIDE(__MEASURE_VALUE - __BASELINE_VALUE, __BASELINE_VALUE)
        )