Forum Discussion
Mesure with complex filters
I am new to DAX, and I want to do a complex filter with it.
The thing is, I have a table :
TableA :
ID
PHONE (phone number of users)
TYPE_PHONE (contain either 0 or 1)
VOLUME_LTE
The phone number it may appear more than once in the table, so I want to count the numbers of users that has a type_phone = 0, and SUM of VOLUME_LTE also equal to 0.
in sql I use this :
Select phone from TableA
where type_phone = 0 and volume_lte = 0
group by phone;
... so you with your last edit.
May be this.
Measure = CALCULATE ( DISTINCTCOUNT ( Table2[PHONE] ), FILTER ( Table2, SUMX ( Table2, IF ( SUM ( Table2[VOLUME_LTE] ) = 0 && Table2[TYPE_PHONE] = 0 && Table2[VOLUME_LTE] = 0, 1, 0 ) ) > 0 ) )Using same sample table I showed before.
Result:
6 Replies
- Chihiro
Solution Sage
Hmm, try following.
Measure = SUMX ( TableA, IF ( SUM ( TableA[VOLUME_LTE] ) = 0 && TableA[TYPE_PHONE] = 0 && TableA[VOLUME_LTE] = 0, 1, 0 ) )
With sample table like below.
Result:
Chihiro by loocking in your example, it doesn't work, because 444111... has 0 in the two rows, but after the mesure it become 2 in total, I tried it and it does not work
- Chihiro
Hmm? what's your condition then?
You had...
type_phone = 0 & volume_lte = 0 & where SUM of VOLUME_LTE = 0...
So... 441112222, has 2 records, and meets all criteria for both rows, i.e. 2.
Since, you updated sql try below then....
Measure 2 = CALCULATE ( COUNT ( [PHONE] ), FILTER ( Table2, Table2[TYPE_PHONE] = 0 && Table2[VOLUME_LTE] = 0 ) )But do note 4441112222 will still return 2 (as per your SQL). 8005002000 will now also return 1.
