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

[farzadvhd]

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]
    )

[govindarajan_d · Accepted 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. 

[govindarajan_d]

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)?

[farzadvhd]

@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

[govindarajan_d · Accepted 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. 

[farzadvhd]

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

[v-yiruan-msft · Accepted solution]

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