Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
@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,
Solved! Go to 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] ) )
)
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
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] ) )
)
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
58 | |
45 | |
42 |