Forum Discussion

boraJustmop's avatar
boraJustmop
Frequent Visitor
3 years ago

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

 

  • 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. 
.............................

 

  •  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

 

2 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    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.

  • boraJustmop's avatar
    boraJustmop
    Frequent Visitor
    3 years ago

    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