Sum with filters but without using filter keyword in DAX

Question

I am trying to find sum with two filters on same column. We can use FILTER keyword, but it takes too much time. So I did following things, but nothing seems to be working. The second filter never gets applied.

1.

MEASURE pop_hd_new[Connectivity %] = CALCULATE(
DIVIDE(
CALCULATE(
       SUM(pop_hd_new[Connected]),
      'prodhier_new'[PROD_TYPE_DESC] in { "Client Solutions PBU", "Enterprise Solution Group PBU"})
   ,CALCULATE(
      SUM(pop_hd_new[SVC_TAG_CNT]),
       'prodhier_new'[PROD_TYPE_DESC] in {"Client Solutions PBU", "Enterprise Solution Group PBU"})
   ))

MEASURE pop_hd_new[Connectivity %] = CALCULATE(
DIVIDE(
CALCULATE(
       SUM(pop_hd_new[Connected]),
       prodhier_new,'prodhier_new'[PROD_TYPE_DESC] = "Client Solutions PBU" || 'prodhier_new'[PROD_TYPE_DESC] = "Enterprise Solution Group PBU")
   ,CALCULATE(
      SUM(pop_hd_new[SVC_TAG_CNT]),
        prodhier_new,'prodhier_new'[PROD_TYPE_DESC] = "Client Solutions PBU" || 'prodhier_new'[PROD_TYPE_DESC] = "Enterprise Solution Group PBU")
   ))

Please suggest some solutions.

How do you know filter is taking too much time? For imported datasets, filter is usually speeding things up. Are you using import or direct query? And can you show your initial approach? — W.B., Feb 12 '21 at 11:33
On a more general note, please include in your question sample source data that you are working on and describe your desired end result. — W.B., Feb 12 '21 at 11:34
If i use below query it's take 75 secs, as i came to know that powerbi is implementing filters on it so i thought why i don't do it by myself and hence query mentioned in que were written. MEASURE pop_hd_new[Connectivity %] = CALCULATE( DIVIDE( SUM(pop_hd_new[Connected]), SUM(pop_hd_new[SVC_TAG_CNT]))) — Rahul Kumar, Feb 12 '21 at 12:30
All i want to do is optimize or reduce the execution time of following query: MEASURE pop_hd_new[Connectivity %] = CALCULATE( DIVIDE( SUM(pop_hd_new[Connected]), SUM(pop_hd_new[SVC_TAG_CNT]))) — Rahul Kumar, Feb 12 '21 at 12:33
Those two look exactly the same to me. Anyway, the queries in your question look wrong with nested CALCULATE put in place of the filter. Please show your data and desired end result. And again - are you using import od direct query? The reason I'm asking is that if it's direct query and filtering slows things down, then maybe it's an issue with indices at your database backend. — W.B., Feb 12 '21 at 12:37

score 0 · Answer 1 · answered Feb 12 '21 at 15:41

You could try this refactoring of your first approach:

MEASURE pop_hd_new[Connectivity %] =
    CALCULATE (
        DIVIDE ( SUM ( pop_hd_new[Connected] ), SUM ( pop_hd_new[SVC_TAG_CNT] ) ),
        'prodhier_new'[PROD_TYPE_DESC]
            IN { "Client Solutions PBU", "Enterprise Solution Group PBU" }
    )

Check for yourself whether it's faster or slower than this FILTER version:

MEASURE pop_hd_new[Connectivity %] =
    CALCULATE (
        DIVIDE ( SUM ( pop_hd_new[Connected] ), SUM ( pop_hd_new[SVC_TAG_CNT] ) ),
        FILTER (
            ALL ( 'prodhier_new'[PROD_TYPE_DESC] ),
            'prodhier_new'[PROD_TYPE_DESC] = "Client Solutions PBU"
                || 'prodhier_new'[PROD_TYPE_DESC] = "Enterprise Solution Group PBU"
        )
    )

Sum with filters but without using filter keyword in DAX

1 Answers1