Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.)
Solved! Go to Solution.
Try:
New Table =
Proud to be a Super User!
Paul on Linkedin.
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)!
Try:
New Table =
Proud to be a Super User!
Paul on Linkedin.
Hi @mm5308 ,
Try this:
Earliest Date result = CALCULATE( MIN ( tablename[posted date] ), ALLEXCEPT(tablename, tablename[id] ) )
This works as a partial solution because it only addresses the "Post" calculation instead of creating the whole table. Nonetheless, good contribution. Thanks.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
87 | |
82 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
60 | |
57 |