topic Re: Changing Filtered Field Dynamically in Dax in DAX Commands and Tips

Changing Filtered Field Dynamically in Dax

boraJustmop — Fri, 23 Dec 2022 08:03:50 GMT

Hi ,

I'm having a problem that I have been trying to solve for a while. I ve been keep searching for the solution but I m not sure if there is a solution.

Let's go through from a simple example. Consider that;

I have 1 Appointment Table, 3 fields ;

Appointment Start Date
Appointment Create Date
Appointment ID

Metric : Distinct count of appointment IDs
lets assume that we filtered "create date" in dax and only showing value for 12th December 2022

I need to change the dax formula dynamically

from

CALCULATE([Bookings Completed #],Appointment[dim_created_date]=datevalue("12/21/2022"))

CALCULATE([Bookings Completed #],Appointment[dim_start_date]=datevalue("12/21/2022"))

to change the date field dynamically I have created a field and a variable as below. When I make a selection vCalendarField works exactly as I want.

.............................

After those steps, I 'm able to change vCalendarfield as I want, dim_start_date or dim_create_date
Then last step; I want to use vCalendarField in Dax as below but I m getting an error.
One last thing, I have apporixametly 100 measures. That is why I dont want to create metrics for both dim_create_date and dim_start_date. So that is why I can't use parameter method.

I just need to change the filtered field in dax dynamicaly using a dynamic variable. Does something like this doable in PowerBI?

Here is the drive link of original document.

https://drive.google.com/file/d/1Vv22OvBE1szkuWDbk6Uy77wGW9VmCJ83/view?usp=sharing

Re: Changing Filtered Field Dynamically in Dax

v-jianboli-msft — Fri, 23 Dec 2022 01:58:55 GMT

Hi boraJustmop ,

Have you ever considered about field parameter?

Field parameters allow users to dynamically change the measures or dimensions being analyzed within a report. This feature can help your report readers explore and customize the analysis of the report by selecting the different measures or dimensions they're interested in.

Here is my sample:

First create a field parameter:

Then create a new table visual:

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Changing Filtered Field Dynamically in Dax

boraJustmop — Fri, 23 Dec 2022 08:00:45 GMT

Thank you so much for your answer and efford.

I can't use field parameter because I will not use create_date and start_date as dimension. I will put Country, City or other fields as dimensions and want to show how many appointments created 12th December 2022 and how many appointments started on the same day based on used dimensions.

That is why I definitaley need to change dax formula dynamically. This is easy to do in Qlik but I couldn't find a way to do it in PowerBI.

Thanks

Oguz