Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Julianavm
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

RoleNameQuarterYearSalary
AnalystMary3202025878
AnalystMary2202023235
AnalystJohn4202033357
AnalystMary1202121031
EngineerBrianna2202044116
EngineerMarcus3202137722
EngineerBrianna1202019660
LawyerPedro4202038006
LawyerPedro3202118822
LawyerMary22020

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
v-xiaosun-msft
Community Support
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".

vxiaosunmsft_0-1671609331649.png

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] ) )

vxiaosunmsft_1-1671609383811.png

 

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.

View solution in original post

1 REPLY 1
v-xiaosun-msft
Community Support
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".

vxiaosunmsft_0-1671609331649.png

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] ) )

vxiaosunmsft_1-1671609383811.png

 

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.