Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

DAX logical AND condition based on multiple selection

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.

4 Replies

  • Thilo's avatar
    Thilo
    Frequent Visitor
    7 years ago
    Hi Grevioos,
    can't you use your SerialNumberSession-Column as a slicer?
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi,

       

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

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    Hi Anonymous 

     

    Can you create a data sample or test pbix file?

     

    Best Regards,
    Mariusz

    Please feel free to connect with me.
    Mariusz Repczynski

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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