cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Need to filter by quarter, year and table specifics

Hello!

I would like some help to create a dax equation to filter by some parameters. I know probably it would be easier with slicers, but, if possible, I would like to not use them at this moment.

Table name: DB

 Role Name Quarter Year Salary Analyst Mary 3 2020 25878 Analyst Mary 2 2020 23235 Analyst John 4 2020 33357 Analyst Mary 1 2021 21031 Engineer Brianna 2 2020 44116 Engineer Marcus 3 2021 37722 Engineer Brianna 1 2020 19660 Lawyer Pedro 4 2020 38006 Lawyer Pedro 3 2021 18822 Lawyer Mary 2 2020 38894

For example, utilizing the sample table above, I want to show the last salary of the quarter of the selected year for Mary to show on a Card visual.

So, for that, I would need to first filter by Role, since we have two Marys, then by her name and year and quarter. I presume.

In this example, I would like the Card to show the value 25878.

I tried like this:

Mary salary = if(
SELECTEDVALUE('DB'[Role])="Analyst",
if(SELECTEDVALUE('DB'[Name])="Mary",
CALCULATE(SELECTEDVALUE('DB'[Salary]), FILTER(ALLSELECTED('DB'),MAX('DB'[Year])&&MAX('DB'[Quarter]))),0),0)

But I didn't have any success.

I'm sorry for any mistakes, I'm very new to Power BI.

Thank you very much for your help!
1 ACCEPTED SOLUTION
Community Support

Hi @Julianavm ,

You can change "SELECTEDVALUE" in CALCULATE function to "MAX" as below.

``````Mary salary =
IF (
SELECTEDVALUE ( 'DB'[Role] ) = "Analyst",
IF (
SELECTEDVALUE ( 'DB'[Name] ) = "Mary",
CALCULATE (
MAX ( 'DB'[Salary] ),
FILTER ( ALLSELECTED ( 'DB' ), MAX ( 'DB'[Year] ) && MAX ( 'DB'[Quarter] ) )
),
0
),
0
)``````

Then select the card and put "Role" and "Name" into filters on this visual. Then select "Analyst" and "Mary".

But when you want to show the salary of others, you may need to change your IF condition. It is less efficient than using slicers.
Below is the way of using slicers. Create a measure as below.

``````Measure =
VAR _a =
SELECTEDVALUE ( 'DB'[Name] )
VAR _b =
SELECTEDVALUE ( 'DB'[Role] )
RETURN
IF ( MAX ( 'DB'[Name] ) = _a && MAX ( 'DB'[Role] ) = _b, MAX ( 'DB'[Salary] ) )``````

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

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

Community Support

Hi @Julianavm ,

You can change "SELECTEDVALUE" in CALCULATE function to "MAX" as below.

``````Mary salary =
IF (
SELECTEDVALUE ( 'DB'[Role] ) = "Analyst",
IF (
SELECTEDVALUE ( 'DB'[Name] ) = "Mary",
CALCULATE (
MAX ( 'DB'[Salary] ),
FILTER ( ALLSELECTED ( 'DB' ), MAX ( 'DB'[Year] ) && MAX ( 'DB'[Quarter] ) )
),
0
),
0
)``````

Then select the card and put "Role" and "Name" into filters on this visual. Then select "Analyst" and "Mary".

But when you want to show the salary of others, you may need to change your IF condition. It is less efficient than using slicers.
Below is the way of using slicers. Create a measure as below.

``````Measure =
VAR _a =
SELECTEDVALUE ( 'DB'[Name] )
VAR _b =
SELECTEDVALUE ( 'DB'[Role] )
RETURN
IF ( MAX ( 'DB'[Name] ) = _a && MAX ( 'DB'[Role] ) = _b, MAX ( 'DB'[Salary] ) )``````

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

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