Forum Discussion

apai1990's avatar
apai1990
Frequent Visitor
2 years ago
Solved

Need help with parameter comparison in Power BI

Hello,

 

I'm a Tableau user and new to Power BI, I got stuck on how to use parameters to create a condition that represents TRUE and FALSE so that I can use them in the filter. I did use the VAR return method but the measure is blank I tried multiple methods but I can't get through or maybe I'm missing something here.

I have parameters "Start year", "End Year", and "Salary period", I want to compare "Start Year" and "End Year" with another table column called "Payment Year" and "Payment periods".

The logic I'm trying to create is as follows:

 

IF [Start Year]=[End Year]

      then [Payment year] = [Start Year] AND [Payment period]= [Salary Period]

ELSEIF 

      [Payment year] = [Start Year] OR [Payment period]>= [Salary Period]

END

 

 Below is the method I applied in Power BI:

 

Time Period = VAR pyS=SELECTEDVALUE('Start Year'[Start Year])
            VAR pyE=SELECTEDVALUE('End Year'[End Year])
            VAR ppS=SELECTEDVALUE('SalaryPeriod'[SalaryPeriod])
            VAR pp=SELECTEDVALUE('Final Table'[Payment Period])
            VAR py=SELECTEDVALUE('Final Table'[Payment Year])
RETURN
IF(pyS=pyE, AND(py=pyS,pp>=ppS),OR(py=pyS,pp>=ppS))

 

Any help will be highly appreciated.

Need Help
  • hackcrr's avatar
    hackcrr
    2 years ago

    Hi, apai1990 

    I have the following data table:

    I have the following two parameters:

    I created a metric using the following DAX expression:

    IsInPeriod = 
VAR pyS = SELECTEDVALUE('Start year'[Start year])  
VAR pyE = SELECTEDVALUE('End Year'[End Year])  
VAR ppS = SELECTEDVALUE(SalaryPeriods[SalaryPeriod])  
VAR pp = SELECTEDVALUE(Payments[Payment Period]) 
VAR py = SELECTEDVALUE(Payments[Payment Year])  
  
RETURN  
    IF (  
        NOT ISBLANK(pyS) && NOT ISBLANK(pyE) && NOT ISBLANK(ppS),  
        IF (  
            pyS = pyE,  
            IF (py = pyS && pp = ppS, "In Period", "Not In Period"),  
            IF (py = pyS || py = pyE || (py > pyS && py < pyE) && pp >= ppS, "In Period", "Not In Period")  
        ),  
        "Parameters Not Set"  
    )

    Use the following fields in the table vision object:

    The results are as follows:

     

     

    Best Regards,

    hackcrr

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

     

2 Replies

  • hackcrr's avatar
    hackcrr
    Icon for Memorable Member rankMemorable Member
    2 years ago

    Hi, apai1990 

    I have the following data table:

    I have the following two parameters:

    I created a metric using the following DAX expression:

    IsInPeriod = 
VAR pyS = SELECTEDVALUE('Start year'[Start year])  
VAR pyE = SELECTEDVALUE('End Year'[End Year])  
VAR ppS = SELECTEDVALUE(SalaryPeriods[SalaryPeriod])  
VAR pp = SELECTEDVALUE(Payments[Payment Period]) 
VAR py = SELECTEDVALUE(Payments[Payment Year])  
  
RETURN  
    IF (  
        NOT ISBLANK(pyS) && NOT ISBLANK(pyE) && NOT ISBLANK(ppS),  
        IF (  
            pyS = pyE,  
            IF (py = pyS && pp = ppS, "In Period", "Not In Period"),  
            IF (py = pyS || py = pyE || (py > pyS && py < pyE) && pp >= ppS, "In Period", "Not In Period")  
        ),  
        "Parameters Not Set"  
    )

    Use the following fields in the table vision object:

    The results are as follows:

     

     

    Best Regards,

    hackcrr

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

     

  • nandic's avatar
    nandic
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    apai1990 i am struggling to understand the output of the logic.
    Dax output is single value, 1 condition.

    Example:
    If you start like this:

    IF(Periods[Start Year]=Periods[End Year],
    now you should write expected output, example: Periods[Start Year] and name of this new column will be "Payment Year".

    In your statement above, instead of 1 output you wrote:

    then [Payment year] = [Start Year] AND [Payment period]= [Salary Period]

    ELSEIF 

    As these are 2 outputs, the logic is not understandable to dax.

    The easiest would be to share screenshot of data and expected output, then it will be clear what to do.

    Cheers,
    Nemanja Andic