Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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]
)
Solved! Go to Solution.
Hi @farzadvhd ,
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.
Hi @farzadvhd ,
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
Hi @farzadvhd ,
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)?
@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
Hi @farzadvhd ,
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.
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
Hi @farzadvhd ,
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
16 | |
13 | |
10 | |
8 |
User | Count |
---|---|
59 | |
20 | |
12 | |
11 | |
10 |