cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## DAX based on the min and max of the selected values in Slicer

I have a calculated column and I want it to be based on the minimum and maximum value selected from the slicer. In fact this formula should work also with the min and max of the slicer instead of the fixed dates. I also attached the sample of the .pbix data HERE

``````quantity_sum_correct =
VAR SelectedStartDate = DATE(2023,2,1)
VAR SelectedEndDate = DATE(2023,3,1)
RETURN
SUMX(
FILTER(
RELATEDTABLE(Quantity),
Quantity[Date] >= SelectedStartDate &&
Quantity[Date] <= SelectedEndDate),
Quantity[quantity]
)``````

2 ACCEPTED SOLUTIONS
Solution Supplier

I see that there is many-to-many relationship between quantity and metal. And this causes duplicates in the table because there is no date column in the metals table. The row duplicates itself for each date that is present in the quantity table. Is this what you want?

During totals, Power BI calculates the sum by having no row level filter on the formula. That is why you are getting 1190.

But I cannot understand how you want the sum to be. Please provide more details.

Community Support

I’ve read through the thread and I see that @govindarajan_d has already provided some insightful responses. 👍

Since you created a calculated column, and it will not change according to the user interaction(slicer, filter, column selections etc.) in the report as the value of a calculated column is computed during data refresh and uses the current row as a context... Please review the following links about the difference of calculated column and measure...

Calculated Columns and Measures in DAX

Calculated Columns vs Measures

So if you want to get the dynamic value base on the selected values, you need to create a measure as below to replace the original calculated column. Please find the details in the attachment.

``````Measure =
VAR SelectedStartDate =
MIN ( Quantity[Date] )
VAR SelectedEndDate =
MAX ( Quantity[Date] )
RETURN
SUMX (
FILTER (
Quantity,
Quantity[Date] >= SelectedStartDate
&& Quantity[Date] <= SelectedEndDate
),
Quantity[quantity]
)
* SUM ( Metals[Gram] )``````
``pz*gr = SUMX ( GROUPBY ( 'Metals', 'Metals'[key], 'Metals'[Metal] ), [Measure] )``

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
Solution Supplier

Calculated columns cannot access slicer value. Is there any specific reason you are using Calculated columns instead of measures? Can the same logic be replicated as a measure (which is more efficient in terms of performance)?

Regular Visitor

@govindarajan_d when I am using measure the total would not be correct, however the values in each row (considering a table in report view) are correct. I appreciate it if you could take a look at the sample I attached to see if there is any other way

Solution Supplier

I see that there is many-to-many relationship between quantity and metal. And this causes duplicates in the table because there is no date column in the metals table. The row duplicates itself for each date that is present in the quantity table. Is this what you want?

During totals, Power BI calculates the sum by having no row level filter on the formula. That is why you are getting 1190.

But I cannot understand how you want the sum to be. Please provide more details.

Regular Visitor

In fact the Metal table does not have any date because it contains different Metals for each Article. So they are in multiple rows and I want the sum to consider these duplicates,  Above all I need pz*gr

I already solved the issue in Excel
By creating a pivot table and xlookup of pivoted table, but I don't know how to apply it in power BI.

Thanks a lot for your help

Community Support

I’ve read through the thread and I see that @govindarajan_d has already provided some insightful responses. 👍

Since you created a calculated column, and it will not change according to the user interaction(slicer, filter, column selections etc.) in the report as the value of a calculated column is computed during data refresh and uses the current row as a context... Please review the following links about the difference of calculated column and measure...

Calculated Columns and Measures in DAX

Calculated Columns vs Measures

So if you want to get the dynamic value base on the selected values, you need to create a measure as below to replace the original calculated column. Please find the details in the attachment.

``````Measure =
VAR SelectedStartDate =
MIN ( Quantity[Date] )
VAR SelectedEndDate =
MAX ( Quantity[Date] )
RETURN
SUMX (
FILTER (
Quantity,
Quantity[Date] >= SelectedStartDate
&& Quantity[Date] <= SelectedEndDate
),
Quantity[quantity]
)
* SUM ( Metals[Gram] )``````
``pz*gr = SUMX ( GROUPBY ( 'Metals', 'Metals'[key], 'Metals'[Metal] ), [Measure] )``

Best Regards

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors