Split the rows with same Date 1 Common and Date 2 is different

Power BI
1

Hello All,

I have the Fact Table Data in the below format.
image

I created a Date Table
Date Table = calendar(DATE(2020,04,01),DATE(2023,12,31))

And Relationship is like
Active Relationship between Fact[Mat Date] - Date Table[Date]
InActive Relationship between Fact[Avail Date]- Date Table[Date]

My scenario is
Capture_2
I have Same Mat Date for a ID twice but the Avail Date is Different.I want to those 2 records .
For Avail Date,
I used the following DAX
Avail Date = var Avail = CALCULATETABLE(sheet2,USERELATIONSHIP(‘Date Table’[Date],Sheet2[Avail Date]))

var Mat = CALCULATETABLE(Sheet2)

return

MAXX(DISTINCT(union(Avail,Mat)),Sheet2[Avail Date])
I’m getting this Output.
image
Can anyone help me out on this.

TIA

PBIX FILE

@ankit

2

I wanna thank you for these files you shared here!

3

If it is in a table visual, can you just add the available date field, it would force the granularity of the table to show all rows, if you use a measure it will calculate something for the current row context but wont create more physical rows, just show an aggregate.

4

Hi @likhitha

We’ve noticed that no response was received from you on the post above.

Just following up if you still require assistance to solve your inquiry.

If yes, can you let us know where you’re stuck and what additional assistance you need?

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

5

Hi @likhitha

We have noticed that your inquiry has remained unresolved for an extended period of time, indicating that it may have been outside the capabilities of our experts and users.

If you believe that you have provided all the necessary context for your concerns and have familiarized yourself with effective forum usage by referring to this guide, we recommend exploring the additional resources included with your subscription. These resources can provide valuable assistance with your Power BI education, and we encourage you to utilize them.

While our users and experts strive to assist with inquiries to the best of their abilities, it is important to acknowledge that not all concerns can be addressed, particularly when there are valuable learning opportunities involved. Thank you for your understanding.

We sincerely hope that you find the solution you are seeking and achieve your desired outcome.