Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello all,
I have a live connection to a SQL table “table1” with historical data.
Example Data:
ID | Status | Start | End | Changed |
1 | A | 2022-05-13 |
| 2022-05-13 |
1 | B | 2022-05-13 |
| 2022-05-14 |
2 | A | 2022-05-15 |
| 2022-05-15 |
3 | A | 2022-05-15 |
| 2022-05-15 |
2 | C | 2022-05-15 | 2022-05-16 | 2022-05-16 |
3 | B | 2022-05-15 |
| 2022-05-16 |
3 | B | 2022-05-15 |
| 2022-05-17 |
3 | C | 2022-05-15 | 2022-05-18 | 2022-05-18 |
4 | A | 2022-05-15 |
| 2022-05-15 |
4 | A | 2022-05-15 |
| 2022-05-16 |
5 | A | 2022-05-16 |
| 2022-05-16 |
What to achieve:
Now, I would like to get the MIN Start-date with Status “A” if the corresponding ID had no other Status since then.
Expected Result:
The output regarding the example table would be 2022-05-15. Solution: ID 4 and ID 5 are the only IDs that did not switch their state. ID 4 provides an earlier start date then ID 5.
I tried different approaches, like comparing a subset of Status A with a subset of other statuses to get the IDs that are in subset A but not in the other one. Another means could be an index if it would be possible to calculate a column.
However, since it is a live connection, I cannot calculate new tables or columns. The DAX measure has to be calculated without those intermediate steps. A change of the connection type is not possible.
I appreciate your help!
Thanks.
Solved! Go to Solution.
Hi,
Please check the below picture and the attached pbix file.
Desired outcome measure: =
VAR _IDunderA =
SUMMARIZE ( FILTER ( Data, Data[Status] = "A" ), Data[ID] )
VAR _IDunderothers =
SUMMARIZE ( FILTER ( Data, Data[Status] <> "A" ), Data[ID] )
VAR _IDonlyA =
EXCEPT ( _IDunderA, _IDunderothers )
VAR _newtable =
CALCULATETABLE ( Data, TREATAS ( _IDonlyA, Data[ID] ) )
RETURN
MINX ( _newtable, Data[Start] )
Hi,
Please check the below picture and the attached pbix file.
Desired outcome measure: =
VAR _IDunderA =
SUMMARIZE ( FILTER ( Data, Data[Status] = "A" ), Data[ID] )
VAR _IDunderothers =
SUMMARIZE ( FILTER ( Data, Data[Status] <> "A" ), Data[ID] )
VAR _IDonlyA =
EXCEPT ( _IDunderA, _IDunderothers )
VAR _newtable =
CALCULATETABLE ( Data, TREATAS ( _IDonlyA, Data[ID] ) )
RETURN
MINX ( _newtable, Data[Start] )
Hi @Jihwan_Kim,
thanks for the solution! I got an additional request and need the ID/IDs according to this MIN value. Tried SELECTEDVALUE with FILTER regarding minx. But it does not work.
Any help is appreciated, thanks!
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
23 | |
10 | |
10 | |
9 | |
7 |