cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Xeeq Frequent Visitor
Frequent Visitor

Aggregate data with identical attributes while keeping the data in sequence using a DAX measure

Below is an image to describe what I need to do with a DAX measure.  I essentially need to get a Sum of values ("Units Sold" in my example below) in a table that are in sequence and where all other column values are repeated.  If any other column value changes, I need a new Sum to start accumulating.

To make this more complex, the value is in a fact table while each of the other columns are in dimension tables.  The final output will be a Table visual with all of the dimension columns together with the fact table value all the way to the right.

 

Example Grouping.jpg

 

Edit: Note that I am using a Live Connection to a Tabular SQL Model, so I am unable to create DAX columns or calculated columns in Power BI.  I'm looking for a way to do this in a DAX measure.  

Edit 2: As I continued to try to write the DAX, I realized something very crucial that I needed to point out here.  My end goal is to have a table as a Power BI visual that shows the data in the correct sequence.  I will be using this table visual to filter other visuals when the user selects rows in the table visual.

2 REPLIES 2
Super User
Super User

Re: Aggregate data with identical attributes while keeping the data in sequence using a DAX measure

Can you post a representative sample of your data that can be copied and pasted? I want to understand how your data is laid out so that I present a solution that is useable. Are Sales Rep, Client and Month all their own dimension tables?

 

In general, what I would do however would be to start a measure with a SUMMARIZE. Then use ADDCOLUMNS to add what you would normally do as a calculated column using EARLIER to figure out if something has changed.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Xeeq Frequent Visitor
Frequent Visitor

Re: Aggregate data with identical attributes while keeping the data in sequence using a DAX measure

Unfortunately, there are many reasons why I cannot post a sample of my data.  Perhaps the most important reason being that I am contractually prevented from doing so.  

Assume that Sales Rep is in the fact table with Units Sold.  Assume that Client is is its own dimension table, and Month is also in its own dimension table.  

Your suggested functions for the basic structure of the measure sound like they might do what I am looking for.  Unfortunately, I have no experience with Add Columns, so I will do some reading and attempt to put something together.

 

Thanks for your help!