Forum Discussion
Helper ICreate New Table Based on DIstinct Values of One Column and Calculated Values of Another Column
I've been searching around for a couple hours now and can't figure out what I'm missing...hopefully someone here can guide me to the right solution.
Out of a large table, I want to create a smaller table summarizing two of the columns (ID and PostedDate). The new table should only contain a row count equal to distinct values from the ID column. Unfortunately, many of the ID values are associated with multiple PostedDate values. To avoid creating a summary of the ID/PostedDate combo distinct values, I only want the new table to contain the earliest PostedDate for each ID.
Here's a sample of the data:
| ID | PostedDate |
| 75 | 1/8/21 |
| 30 | 1/15/21 |
| 30 | 1/16/21 |
| 98 | 1/18/21 |
| 98 | 1/16/21 |
| 98 | 1/16/21 |
| 98 | 1/17/21 |
Note that it has only 3 distinct ID values but the second and third ones contain multiple PostedDate values each.
Ideally, here's the output I want:
| ID | PostedDate (Earliest/Min) |
| 75 | 1/8/21 |
| 30 | 1/15/21 |
| 98 | 1/16/21 |
I've tried several iterations of formulas to create a table but they all result in the same error, which is that the earliest PostedDate of the entire original table (1/8/21) is reapeated on all rows instead of the earliest PostedDate for each individual ID. Below are some examples of DAX I've tried.
New Table =
The output of any of these looks like the following:
| ID | PostedDate |
| 75 | 1/8/21 |
| 30 | 1/8/21 |
| 98 | 1/8/21 |
What am I doing wrong? (To emphasize again, I am looking to create a table here, not a measure.)
Try:
New Table =
ADDCOLUMNS(DISTINCT('Table'[ID]),"Post", CALCULATE(MIN('Table'[PostedDate]), ALLEXCEPT(Table, Table [ID]))
4 Replies
- mm5308
This works as a partial solution because it only addresses the "Post" calculation instead of creating the whole table. Nonetheless, good contribution. Thanks.
- mm5308
Thank you both for your replies! The accepted solution worked perfectly. When I first saw the new table, I was scared because all the visible "Post" dates were all the same still. But when I clicked the drop-down arrow on that column, it showed that there were indeed a ton of PostDate values. The metadata at the bottom confirmed as much.
Realizing this "check the data" error, I wondered if my original DAX expressions worked without me realizing it because I'd focused only on the visible results. Turns out, the third expression above (that starts with SUMMARIZE) actually did work. I feel silly for posting now.
Again, thanks to the two users who helped. More than a simple solution to this particular problem, I learned a more valuable lesson: check my work better (and don't be in a rush)!
PaulDBrownCommunity Champion
Try:
New Table =
ADDCOLUMNS(DISTINCT('Table'[ID]),"Post", CALCULATE(MIN('Table'[PostedDate]), ALLEXCEPT(Table, Table [ID]))
