cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
at01
Frequent Visitor

How to use filters in the measures

Hello,

 

I am discovering DAX and I would need some help understanding what I do wrong in building my measures.

 

I have the following measure:

-------------------------------------------------------------------------------------

Contacted Clients =
var startDate=min('Date'[Date])
var endDate=max('Date'[Date])
var countClients=distinctcount(Communication[Client ID])
Return
calculate( countClients,
filter(Communication, Communication[Message Created At] >= startDate && Communication[Message Created At] <=endDate)
)

-------------------------------------------------------------------------------------

which counts the number of clients that have been contacted during the period between the dates that are choosen by the user (based on the Date[Date] column.

 

I want to also calculate the number of clients that have been contacted 30 days prior to the first date choosen by the user. SO I defined the following measure:

-------------------------------------------------------------------------------------

Contacted Clients 30 days prior=
var startDate=min('Date'[Date])-30
var endDate=min('Date'[Date])
var countClients=distinctcount(Communication[Client ID])
Return
calculate( countClients,
filter(Communication, Communication[Message Created At] >= startDate && Communication[Message Created At] <=endDate)
)

-------------------------------------------------------------------------------------

but I get the same number in Contacted Clients and Contacted Clients 30 days Prior

 

I suppose I do not understand exactly how it should work, any tips on what needs correcting? Is there an issue that the Date and Communication tables are linked using a Many to one relationship table between Communication[Message Created At] and Date[Date] column?

 

I have tried ALL(Communication) as well to make sure to ignore the filters but I suppose it is not the issue.

 

Thank you very much for any tips on how to correct this!

1 REPLY 1
AlB
Super User III
Super User III

Hi @at01 

VAriables in DAX are immutable. Their value won't change after declaration. So that calculate will not have any effect whatsoever. Try this: 

Contacted Clients =
VAR startDate =
    MIN ( 'Date'[Date] )
VAR endDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Communication[Client ID] ),
        FILTER (
            Communication,
            Communication[Message Created At] >= startDate
                && Communication[Message Created At] <= endDate
        )
    )

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors