cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Aakash_Ladi
Regular Visitor

Can we have a common DAX expression which can be used in multiple measures

I have measures in which I am using DAX to filter two fact tables and then perform some aggregation. I have written the same filtering condition in almost 50 measures. Is it somehow possible that I can save these filtering conditions somewhere outside of measure(something like Global Variable)?

2 REPLIES 2
FreemanZ
Community Champion
Community Champion

This is a legitimate request, but there is no Global Variable in DAX. 

Do you think there is any workaround for this?

My measures are something like this:

abc = CALCULATE(SUM(column_nme),

FILTER(factTable1, pointDate >= factTable1[Date1] && pointDate <= factTable1[Date2]),

FILTER(factTable2, pointDate >= factTable2[Date1] && pointDate <= factTable2[Date2]),

TREATAS (values(Date1[col1]), factTable2[DateCol1]),

TREATAS (values(Date2[col2]), factTable1[DateCol1])

)

 

There are 40-50 such measures and users want to see at least 10 of them together. Now, When I am putting them together in a matrix visual, they are taking a long time to evaluate, 20 mins to be accurate. I think this is due to filtering two fact tables at once(both the fact tables are huge, 15 million rows in one and 5 million in another) and that too for every measure. Do you think there is any way that I can optimize the performance?

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors