Forum Discussion

imadelmouden's avatar
imadelmouden
Frequent Visitor
7 years ago
Solved

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 ph...
Need Help
Show and Tell
  • Chihiro's avatar
    Chihiro
    7 years ago

    ... 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's avatar
Chihiro
Icon for Solution Sage rankSolution Sage
7 years ago

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's avatar
imadelmouden
Frequent Visitor
7 years ago

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's avatar
    Chihiro
    Icon for Solution Sage rankSolution Sage
    7 years ago

    ... 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's avatar
    Chihiro
    Icon for Solution Sage rankSolution Sage
    7 years ago

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