March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
I have a simple question in DAX.
Let's say if I have a Revenue table with customers and the revenue generated by them for each day in a quarter.
Like
Customer Date Revenue
A 01/01 23
B 01/01 24
C 01/01 25
.
.
.
.
.
.A 03/31 45
B 03/31 32
C 03/31 27
And I have a customers table which will hold only the unique names of the customers in a column. And I also have a relationship between the customers table and revenue table on the customers column.
If I have a date slicer in my report, how do I create a calc column in this customers table, that will show the total revenue of the corresponding customer for the dates provided by the data slicer ?
I tried CALCULATE(SUM(Revenue[Revenue])) which is the basic formula, but it gives me the total revenue instead of just the revenue for the selected dates. How do I fix this ? I am sure that I am missing something very simple.
Which date field are you using in your date slicer? The Date field from the revenue table? or from another table?
From the same Revenue table.
In theory that should be enough. The slicer will just restrict the rows from the Revenue table that are visible to the SUM function.
But that is not happening as expected. It is still returning the sum of all revenue from all dates instead of just the selected dates. I wonder why this is happening to all my date slicers
Same thing is happening with filters on other columns as well. If I have column called Category, and if I use a filter of Category in my report, it is not having any effect on the revenue column in my customers table. Whereas it does affect the revenue column in the revenue table.
No, there is only one column with Revenue as it's name and it is the one that I created
You haven't inadvertanly used the "edit interactions" feature to disable filtering of your visual by the slicer?
Oh I see.
Try a calculated measure like this..
Measure = CALCULATE(SUM('Customer'[Revenue]),RELATEDTABLE(Customer))
Hey, can you please explain why you created the calculation in your customer table?
The "normal" way is to use columns from different tables in the same table, but I also have to admit, that I do not know what is "normal" for sure
The reason for creating a seperate column is that, in my base table, I have granularity on date level. I needed an aggregate view of the revenue for each customer and I should be able to assign a range based on the revenue generated by them.
If I do all this in my base table, I need to go doing it like this:
In an attempt to fix all this, I am trying to create a custom table that will store the aggregated values which will change automatically as the date slicer is adjusted.
Hm,
"Aggregate a Measure ... not working"
This is not a fundamental law :-), just not that obvious and can become very mindboggling
You can do something like
1. Create a Measure
2 Create a 2nd measure that uses a pattern like this
Measure 2 = SUMX( ADDCOLUMN( SUMMARIZE( ) ,"A funny name", [Measure 1] ) , Do something with the column "A funny name" in an if statement )
Consider to create sample data, upload a pbix to onedrive and share the link. This sounds like an interesting challenge. Also consider the new DAX formula SUMMARIZECOLUMNS, this formula is described here
https://www.sqlbi.com/articles/introducing-summarizecolumns/
Regards
Tom
Regards
Tom
@TomMartensI am not sure if I follow the pattern mentioned by you for the second measure.
I actually tried creating a different column based on the measure using the VALUE function earlier but it did not work. I used the below formula format.
Range as CalcColumn = VALUE([Range as Measure])
And for the sample file, I had a question about this yesterday and I've uploaded the file here in an another question
https://1drv.ms/u/s!AmE9ILAWJzWtgf9JbHAH0Oh_z3MIOw
Link to that discussion I had with @Phil_Seamark yesterday
https://community.powerbi.com/t5/Desktop/ISFILTERED-Usage/m-p/381985
Measure will work. But I need a calc column for other purposes. I need to be able to assign a range for this revenue column for each company. And if I use a measure, I ran into other issues earlier where in the range was not being assigned properly
A calculated column would use the same syntax.
But values generated by calculated columns are static and cannot be modified by a filter. Filters will only control which rows are visible to DAX functions.
New Column in Revenue table = CALCULATE(SUM('Customer'[Revenue]),RELATEDTABLE(Customer))
A calculated column will never "adjust" to selections from a slicer, this is a fundamental law in the data model of Power BI and also of SSAS Tabular.
So it seems you have to think outside the box
No, AFAIK, I did not disable any interactions from visuals. Even then, if that is case, the slicing should not work when I use the actual column with the customer name in my "Customer" table, isn't it ?
Hey,
I'm wondering what will happen, if you create a table visual that contains the customer column form the customer table and the revenue column from your revenue table, not the measure you calculated.
Basically the above mentioned should just provide what you are looking for, this is odd
And can you also pleases make a screenshot from your table relationships pane
Regards
Tom
I actually tried that way. It works as expected when I pull customer name from my Customer table and Revenue from Revenue table. But the custom calc column that I created doesn't behave in the same way.
Attached the relationship.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
94 | |
89 | |
86 | |
77 | |
49 |
User | Count |
---|---|
164 | |
149 | |
101 | |
73 | |
56 |