Slicer selected value can't be used in calculated columns or measures

[Anonymous]

Hi there,

I'm discover PowerBI since 2 hours and just hit my first issue I couldn't solve using other posts.

Here is the context :

The master table contains a list of activities :

- activity_owner (owner of the activity)

- activity_added_at (when the activity is created)

- activity_type (e.g call_1, reach_call_1,...)

- deal_added_at (when the client linked to the activity was created)

Two calculated columns (so far) :

- calls, which returns 1 if the activity_type is a call, else 0

- reach_calls, which returns 1 if the activity_type is reach_call, else 0

What I'm trying to do :

- build a table (on the report) with activity_owners

- the count of activities by type (e.g count_call_1, count_reach_call_1,...) per owner

- the count of deals 

- between two dates using a slicer

The first difficulty was to create an "independant" slicer, i.e not refering to activity_added_at or deal_added_at.

For instance, I want to display the count of call_1 with an activity_added_at between date1 and date2, and at the same time, the count of deals with a deal_added_at between the same date1 and date2. 

To do so I built 6 parameters (year1, month1, day1, year2, month2, day2) and used them in a slicer, so that the report's user can pick the date range.
I have also created 2 measures (date1 = date(year1, month1, day1) and same for date2) and wanted to use them in calculations.

For instance I tried to build a count measure :

CALCULATE(SUM(calls), activity_added_at >= date1, activity_added_at <= date2)) but it doesn't work.

I also noticed that date1 and date2 returns null when I try to use them in calculated columns (but display the expected value on the report)

I can provide the .pbix file if needed, but don't know how to do so 😞 

Many thanks in advance

[Anonymous]

Hi,

Thanks for quick and useful reply.

I've created a separate date table following this DAX expression I found on the web :

Date =
VAR __startDate = DATE ( 2022, 1, 1 ) 
VAR __endDate = DATE ( YEAR ( TODAY() ), 12, 31 ) 
VAR __dates = CALENDAR ( __startDate, __endDate )
RETURN
ADDCOLUMNS (
    __dates,
    "Year",             YEAR ( [Date] ),
    "Month Number",     MONTH ( [Date] ),
    "Month Name",       FORMAT ( [Date], "MMMM" ), --use MMMM for full month name, January instead of Jan
    "Month",            FORMAT( [Date], "MMM, YYYY" ), --use MMMMM for full month name, January instead of Ja
    "Month Sort",       FORMAT( [Date], "YYYY-MM" ),
    "Quarter",          "Q" & FORMAT( [Date], "Q, YYYY" ),
    "Quarter Sort",     FORMAT ( [Date], "YYYY-Q" )
)

I set up a slicer using the "Date field". 

Now, I wonder how to "re-create" my date1 and date2 so that I can use them in measures with the formula you provided

[pmreis]

Hi @Anonymous 

• Instead of creating six parameters for year, month, and day, consider creating a separate date table that spans the range of dates you expect to work with. This table will not be directly related to your data but will provide a continuous range of dates for your slicer.
• Once you have this date table, you can use it for your slicer, allowing users to select a date range.
• Instead of trying to use the measures date1 and date2 in a calculated column, use them directly in measures.
• Try something like this:

Count Calls Between Dates = 
CALCULATE(
    SUM('YourTableName'[calls]), 
    'YourTableName'[activity_added_at] >= [date1], 
    'YourTableName'[activity_added_at] <= [date2]
)
​

Pedro Reis - Data Platform MVP / MCT
Making Power BI and Fabric Simple

If my response resolved your issue, please mark it as a solution to help others find it. If you found it helpful, please consider giving it a kudos. Your feedback is highly appreciated!

Find me at LinkedIn