Calculate: Filter criteria not being applied

DAX DAX Calculations
1

In this case, I’m seeking to count any Company ID that meets the criteria for exceeding a fixed amount (in this case, the simple criterion of “Total Minutes” > 2000). But the DAX formula is skipping rows where the criterion is plainly met:

image

I have tried specifying additional table granularity, but that attempt results in an error:

image

2

Dear mdalton2100

Thanks for sharing your issue. It would be great if you can upload your pbix file so that we can help you better.

Best

3

Thanks for pointing that out. I have uploaded an anonymized mock-up PBIX file, which summarizes the problem (#1) in its own Report Page. It also simulates the solution. Goal is to display the total in a Power BI Card view. Another problem, perhaps related, is shown in the second report page. Thanks in advance!

Data Model with Mockup.pbix (3.0 MB)

4

Thanks for uploading your file.
You just need to use measures instead of calculated columns.
What I did is that I create a new measure called “Total Minutes Hossein” using your calculated column “Total Minutes_m”, and then used it in the “Developers Engaged-Hossein” measure.
I hope it would be helpful.

Total Minutes Hossein =
SUM( ‘Data Model _ mock’[Total Minutes_m] )

and then

Developers Engaged-Hossein = CALCULATE (
DISTINCTCOUNT( ‘Data Model _ mock’[CompanyId] ),
FILTER( VALUES( ‘Data Model _ mock’[CompanyId]) ,
[Total Minutes Hossein] > 2000))

Untitled
Untitled606×520 19.9 KB

This is the pbix file: Data Model with Mockup - Hossein.pbix (3.0 MB)

Best Regards
Hossein

6 Likes
5

Thanks Hossein, works beautifully. (I do need to gain a better understanding of when to use column calculations versus DAX measures). One more thing: How do I hard-code a fixed Date filter (of > 5/15/20) into the DAX measure itself, so that setting a Pane filter criterion can be avoided? Somehow, my syntax must be wrong. Also, see below re: additional problem (#3) of applying a second filter criterion:

image

In addition (per an update of your PBIX file), I’m not able to add another criterion whereby “InTrial” column = 0. This issue is explained in Page 3. Thanks!

Data Model with Mockup - Hossein.pbix (3.0 MB)

1 Like
6

It’s great to know that you are making progress with your query @mdalton2100 . Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

7

Dear mdalton2100
This is so easy in Power BI to filter dates in a measure. You just need to add a line to your code. I did It as you can see below, but the answer was 92 showing that I did not get the right answer (85). The result of the measure I wrote is different from what you got by using filter pane.
I used this code for your example:

Paid Users Hossein = 
CALCULATE (
  DISTINCTCOUNT( 'Data Model _ mock'[CompanyId] ),
    FILTER( all( 'Data Model _ mock'[CompanyId]) , 
    [Total Minutes Hossein] > 2000) , 
    'Data Model _ mock'[InTrial] = 0,
     Dates[Date] >= DATE(2020,5,15))

In the last line of the above code, I filtered the date table for the dates on or after 2020/05/15.
I dont know why it doesn’t work but I hope you will find the right answer.

Untitled
Untitled1366×728 81.2 KB

8

Thanks again. Per the validation visual below, I believe the correct answer is “69”. When I apply the usual filter pane parameters to your latest measure, the 95 then changes to 69, as shown below. Could it be my validation visual is not a valid benchmark? I have re-uploaded the updated PBIX file.Data Model with Mockup - Hossein.pbix (3.0 MB)

image

image
image528×519 22.9 KB

9

Hi @mdalton2100

Try below measure.

Paid Users (Hossein) = 
VAR __BaseTable =
    FILTER (
        'Data Model _ mock',
        'Data Model _ mock'[InTrial] = 0
            && 'Data Model _ mock'[Date] >= DATE ( 2020, 5, 15 )
    )
VAR __FinalTable =
    FILTER (
        SUMMARIZE (
            __BaseTable,
            'Data Model _ mock'[CompanyId],
            "@TotMin", SUM ( 'Data Model _ mock'[Total Minutes_m] )
        ),
        [@TotMin] > 2000
    )
RETURN
    CALCULATE ( DISTINCTCOUNT ( 'Data Model _ mock'[CompanyId] ), __FinalTable)
1 Like
10

Thank you anki, that did solve the problem! For my own education, any comment on why this solution was effective?

11

Hi @mdalton2100

I have replicated what you are doing using Visual level filters . Please find explanation below

__BaseTable = To Create a Virtual table after applying InTrial and Date filters on Mockup table.
__FinalTable - From Filtered table, I am extracting CompanyID with minutes greater than 2000.

First using SUMMARIZE to Group by CompanyID and find Sum of minutes. Then Filter to extract only those CompanyID with Sum of Minutes > 2000

Last Step in Return is calculate to extract DistinctCount of Customers from __FinalTable.

Do let me know if anything is not clear.

Thanks
Ankit J

12

Thank you ankit, that was most helpful.

I have one more DAX problem I’m trying to solve, as shown below. I’m sure the answer is straightforward. Here is a revised mock PBIX file: Data Model with Mockup _ weekly delta in count.pbix (3.0 MB)

image

13

Hi @mdalton2100

This post has been marked as resolved. Please raise a separate post for new query so others can also help/guide you.

Thanks
Ankit J

1 Like