Dynamic Filtering in DAX

[ncbshiva]

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

IP	Name	Type	Category	row#	measure
Ip1	SMB Scan	Information	Cat1	1
IP1	Name 2	Vulnerability	Patch	2	X
IP1	Name3	Vulnerability	Cat3	3
Ip2	Name4		Cat4	4
Ip2	Name5		patch	5

Please let me know how to achieve this in DAX.

Regards

SHIVA

[Anonymous]

// 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

[lbendlin · Accepted solution]

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"