Help with my (multiple functions) formula please?

[Anonymous]

@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

refSCRIPT_VALIDATION

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,

[Anonymous]

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 · Accepted solution]

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