Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
boraJustmop
Frequent Visitor

Changing Filtered Field Dynamically in Dax

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  ;
  1. Appointment Start Date
  2. Appointment Create Date
  3. 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

boraJustmop_1-1671722884750.png

 

  • I need to change the dax formula dynamically

         from

         CALCULATE([Bookings Completed #],Appointment[dim_created_date]=datevalue("12/21/2022"))
         to 
         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. 
boraJustmop_2-1671723232286.png.............................boraJustmop_5-1671723687831.png

 

  •  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. 
  • boraJustmop_6-1671724091605.png
  • 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

 

2 REPLIES 2
boraJustmop
Frequent Visitor

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

 

 

v-jianboli-msft
Community Support
Community Support

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:

vjianbolimsft_0-1671759375489.png

First create a field parameter:

vjianbolimsft_1-1671759436959.png

Then create a new table visual:

vjianbolimsft_3-1671760649968.png

Final output:

vjianbolimsft_4-1671760667609.png

vjianbolimsft_5-1671760698232.png

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors