Forum Discussion

ncbshiva's avatar
ncbshiva
Advocate V
6 years ago
Solved

Dynamic Filtering in DAX

Hi All,

 

Below is my sample dataset, the requirement is i need to check all the IPs where Name="SMB Scan" and need to mark them as "X" where Category="Patch" for those IPs only where Name="SMB Scan".

 

In the below sample, i need to mark row2 as "X" by creating a measure

 

IPNameTypeCategoryrow#measure
Ip1SMB Scan InformationCat11 
IP1Name 2VulnerabilityPatch2X
IP1Name3VulnerabilityCat33 
Ip2Name4 Cat44 
Ip2Name5 patch5 

Please let me know how to achieve this in DAX.
 
Regards
SHIVA
DAX
  • lbendlin's avatar
    lbendlin
    6 years ago

    Here's a crude-ish way of doing that

     

     

     

    Measure = 
var ip=SELECTEDVALUE(Table[IP])
var c=COUNTX(FILTER(ALL(Table),Table[IP]=ip && Table[Name]="SMB Scan"),1)
return c=1 && SELECTEDVALUE(Table[Category])="Patch"

     

     

     

     

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    6 years ago

    Here's a crude-ish way of doing that

     

     

     

    Measure = 
var ip=SELECTEDVALUE(Table[IP])
var c=COUNTX(FILTER(ALL(Table),Table[IP]=ip && Table[Name]="SMB Scan"),1)
return c=1 && SELECTEDVALUE(Table[Category])="Patch"

     

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    // It would be much better to have a measure
// that would return the number of IP addresses
// contained in the current context that do 
// have the required characteristics. From such
// a measure it's easy then to obtain what you want
// but the measure would be much more general.
// T is your table. So, here it is:

[# IPs] =
	CALCULATE(
		DISTINCTCOUNT( T[IP] ),
		KEEPFILTERS(
			T[Name] = "SMB Scan"
		),
		KEEPFILTERS(
			T[Category] = "Patch"
		)
	)
	
// Your X measure would then be:

[X] =
	var __shouldCalculate =
		HASONEFILTER( T[IP] )
		&& HASONEFILTER( T[Name] )
		&& HASONEFILTER( T[Category] )
	return
		if( __shouldCalculate,
			if( [# IPs] > 0, "X" )
		)

     

    Best

    D