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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Help with my (multiple functions) formula please?

@Anonymous

Thanks for your response/effort but it doesn't resolve my issue.

As mentioned, I initially provided a rushed description on what I am trying to acheive. I will explain the issue faced in detail (as I should). Please see the below detailed requirement (it overwrites the initial rushed description).

-------------------------------------------------------

I have two tables as follows:

refExpected

refExpected.JPG

 

refSCRIPT_VALIDATION

refSCRIPT_VALIDATION.JPG

 

 

The active relationship between the two tables is refExpected.Session = refSCRIPT_VALIDATION.Session_name. There is an inactive relationship of refExpected.SessionQualification = refSCRIPT_VALIDATION.SessionQualification.

 

There are two filters on the report, which firstly is refSCRIPT_VALIDATION.Session_name and secondly the refSCRIPT_VALIDATION.Qualification. Well actually these filters will be parameters but until I get this issue resolved I will keep them as filters for simplicity.

 

The Session_name filter is mandatory and will always be populated. The Qualification filter is optional.

 

I have card visual showing the value of a measure. The latest variation of my measure is below:

mScriptsExpected = 
IF( ISFILTERED( refSCRIPT_VALIDATION[Qualifications.Qualification] ),
    CALCULATE( 
                       SUM( refExpected[Expected] ),
                       ALLEXCEPT( refSCRIPT_VALIDATION, refSCRIPT_VALIDATION[SessionQualification] )
                       )
   ,
   CALCULATE( SUM( refExpected[Expected] ), ALLEXCEPT( refSCRIPT_VALIDATION, refSCRIPT_VALIDATION[session_name] ) )
  )

 

If only the Session filter (June 2017) is populated then I need the total for refExpected.Expected value for that session - based on the above refExpected table the  value returned will be 4545. If the Qualification filter is populated (GCSE) too then I require the total refExpected.Expected for June 2017 and GCSE - based on the above table will be 1111.

 

I have created an inactive relationship based on two concatenated fields should I need it.

How to I achieve what I am trying to do?  Do I need the inactive relationship I created or can I remove it?

Thanks in advance,

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I have resolved this myself. So to help others if they need I have placed the working formula below.

 

mScriptsExpected =
          IF( ISFILTERED( refSCRIPT_VALIDATION[Qualifications.Qualification] ),
             CALCULATE(
                                SUM( refExpected[Expected] ),
                                FILTER( refExpected, refExpected[Session] = refSCRIPT_VALIDATION[ScriptSession] ),
                                FILTER( refExpected, refExpected[Qualification] = refSCRIPT_VALIDATION[ScriptQualificaton] )
                                )
            ,
CALCULATE( SUM( refExpected[Expected] ), ALLEXCEPT( refSCRIPT_VALIDATION, refSCRIPT_VALIDATION[session_name] ) )
)

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

HI @Anonymous,

 

If you mean you want to filter on multiple columns when you filter on Qualification column, I'd like to suggest you add session to allexcept function to apply filter effect:

 

mScriptsExpected =
IF (
    ISFILTERED ( refSCRIPT_VALIDATION[SessionQualification] ),
    CALCULATE (
        SUM ( refExpected[Expected] ),
        USERELATIONSHIP ( refSCRIPT_VALIDATION[SessionQualification], refExpected[SessionQualification] ),
        ALLEXCEPT (
            refSCRIPT_VALIDATION,
            refSCRIPT_VALIDATION[SessionQualification],
            refSCRIPT_VALIDATION[session_name]
        )
    ),
    CALCULATE (
        SUM ( refExpected[Expected] ),
        ALLEXCEPT ( refSCRIPT_VALIDATION, refSCRIPT_VALIDATION[session_name] )
    )
)

 

If above not help, please share more detailed information to help us clarify your scenario.

How to Get Your Question Answered Quickly

 


Regards,

Xiaoxin Sheng

Anonymous
Not applicable

I have resolved this myself. So to help others if they need I have placed the working formula below.

 

mScriptsExpected =
          IF( ISFILTERED( refSCRIPT_VALIDATION[Qualifications.Qualification] ),
             CALCULATE(
                                SUM( refExpected[Expected] ),
                                FILTER( refExpected, refExpected[Session] = refSCRIPT_VALIDATION[ScriptSession] ),
                                FILTER( refExpected, refExpected[Qualification] = refSCRIPT_VALIDATION[ScriptQualificaton] )
                                )
            ,
CALCULATE( SUM( refExpected[Expected] ), ALLEXCEPT( refSCRIPT_VALIDATION, refSCRIPT_VALIDATION[session_name] ) )
)

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors