DAX logical AND condition based on multiple selection

[Anonymous]

Hi,

Lets say this is a sample of my data. I have a slicer that allows to choose multiple values based on FailureCode column. One Session can have multiple FailureCodes. What I need to achieve, is to get only those sessions, that have all failures codes which are selected.

So I this case, If I select 10.0.4 and 11.0.5 values on my slicer, I want to get all rows where session is 1, as this is the only one that fulfills this condition. If I select only 10.0.4 I should get rows with Session 1 or 2.

I am quite inexperienced with DAX so I've tried to find some tips and ultimately came up with this one. 

Relevant =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            VALUES ( Table[SerialNumberSession] ),
            "Session", CALCULATE (
                COUNTROWS ( VALUES ( 'FailureCode'[FailureCode] ) ),
                CALCULATETABLE ( Table)
            )
        ),
        [Session] = COUNTROWS ( VALUES ( 'FailureCode'[FailureCode]' ) )
    )
)

I have created additional column called "SerialNumberSession" which is concatenation of SerialNumber and Session (those are not unique by themselves). I have also created table called "FailureCode" which holds only unique values of FailureCode column.

Thank you for any advice/tip on what do I do wrongly.

[Anonymous]

Mate, first of all, measures do not return tables. Only single values.

Second of all, here's a measure that will tell you for each session whether it has all the codes selected in your dimension:

[Session Has All Selected Codes] =
var __visibleFailureCodes = VALUES ( FailureCodes[FailureCode] ) -- note the "s" on the end (dims should end in "s")
var __numOfVisibleCodes = COUNTROWS ( __visibleFailureCodes )
var __oneSessionVisible = HASONEVALUE ( FactTable[Session] )
var __existingCodesForSession =
	CALCULATETABLE (
		VALUES ( FactTable[FailureCode] ),
		VALUES ( FactTable[Session] ),
		__visibleFailureCodes,
		ALL ( FactTable )
	)
var __numOfExistingCodes = COUNTROWS ( __existingCodesForSession )
var __sessionHasAllSelectedCodes = ( __numOfExistingCodes = __numOfVisibleCodes ) 
return
	if ( __oneSessionVisible, __sessionHasAllSeletedCodes )

Best

Darek

[Mariusz]

Hi @Anonymous 

Can you create a data sample or test pbix file?

Best Regards,
Mariusz

Please feel free to connect with me.

[Thilo]

Hi Grevioos,
can't you use your SerialNumberSession-Column as a slicer?

[Anonymous]

Hi,

This wouldn't solve the problem as I want to see all the sessions that have all selected FailureCodes.