Mesure with complex filters

[imadelmouden]

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;

[Chihiro]

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:

[imadelmouden]

@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.

[imadelmouden]

@Chihiro I think I didn't explain enough, I don't want to count how many times the phone appear with those conditions, I want to count JUST the number that has 0 total volume summarized,

phone         volume_lte
99999999    45

44455555    0

55555555    15

44455555    0

55555555    0

So the output here is : 1 because the only number that has 0 volume in every row, is the 4445555
I hope I am clear now

[Chihiro · Accepted solution]

... 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:

[Chihiro]

? Still isn't very clear to me what your requirement is.

May be upload sample data set, that mirror your data structure, along with manually counted/calculated expected output.