Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi, I believe similar questions have been tackled before but I have not found workable answers yet. Please help.
I have uploaded a simple example here:
https://www.dropbox.com/sh/crx5heiyhblxsf8/AADVmshkzf11QKfKFveawzQma?dl=0
I use the date slicer to filter the transactions and I need to hide blank rows but I want the total to sum up values since inception.
I'm new to Dax so would appreciate if someone could explain the logic of the calculations.
I'm currently under the impression that calculate(sum([field], all(table[date])) would create a temporary table with all dates and then sum up values, but that's not how it works obviously.
Thanks a lot for your help and time!
Solved! Go to Solution.
Hello @fess440
Try adding these two measure. The first one is just to sum the value in transations so we can us that in the next measure.
Total Value = SUM ( transactions[value] )
The next one checks if it is on a line in the visual where that line is for a singel customer or event and if so returns the [Total Value] measure, the actual filtered amount by date, customer, event. If not it returns the total of all transactions, the 12286.
Total Measure = IF ( ISINSCOPE( customers[name] ) || ISINSCOPE( events[event] ), [Total Value], CALCULATE( [Total Value], ALL ( transactions ) ) )
any advise anybody, please? i'm pretty stuck. thanks.
Hello @fess440
Try adding these two measure. The first one is just to sum the value in transations so we can us that in the next measure.
Total Value = SUM ( transactions[value] )
The next one checks if it is on a line in the visual where that line is for a singel customer or event and if so returns the [Total Value] measure, the actual filtered amount by date, customer, event. If not it returns the total of all transactions, the 12286.
Total Measure = IF ( ISINSCOPE( customers[name] ) || ISINSCOPE( events[event] ), [Total Value], CALCULATE( [Total Value], ALL ( transactions ) ) )
@fess440 -
First, you asked about CALCULATE - using ALL within the second parameter of CALCULATE modifies the "filter context" to ignore any filter on the specified table or column.
Secondly, could you provide the expected results? I'm not sure what the problem is with the calculation.
@Anonymous the expected value of the total should be 12,286 at all times regardless of the dates selected in this example. it is the total value when the date slicer is set from 01/01/2019 - 12/05/2020. i also need a solution that will work accordingly for subtotals because this is just an example and i have several hierarchies. hope this is clear.
@fess440 - It will work if you add a separate date table and use that date for your slicer, for the value in the table visual, and for the filter in the table visual. Please try that and let us know whether it works.
@Anonymous i'm not sure if i have understood you correctly but this is what i've done:
1. created date table linked to transaction table
2. changed slicer to have dates from date table instead
3. changed measure to be