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

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