topic Re: Dynamic Filtering in DAX in DAX Commands and Tips

Dynamic Filtering in DAX

ncbshiva — Thu, 25 Jun 2020 13:26:18 GMT

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

Re: Dynamic Filtering in DAX

lbendlin — Thu, 25 Jun 2020 19:39:18 GMT

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"

Re: Dynamic Filtering in DAX

Anonymous — Mon, 29 Jun 2020 11:57:26 GMT

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