Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
farzadvhd
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

Hi @farzadvhd ,

 

govindarajan_d_0-1706784885876.png

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. 

View solution in original post

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

vyiruanmsft_0-1706861052794.png

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.

View solution in original post

5 REPLIES 5
govindarajan_d
Solution Supplier
Solution Supplier

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 ,

 

govindarajan_d_0-1706784885876.png

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

vyiruanmsft_0-1706861052794.png

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.