March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi,
I have a Power BI File with "Calendar" table with Date Column & another table called "Tbl_INC_SR_CR_Tickets_Query" with columns "Opened_Date", "Closed_Date" & "CarriedForward_Date". Relationship in Calendar Table & "Tbl_INC_SR_CR_Tickets_Query" is on "Opened_Date". I have a page with 1 Slicer which has months from "Calendar" table, & another table visual in which i want to show how many tickets are opened in the month which is selected from slicer, how many tickets are closed in the month which should also be selected from the same slicer, & also how many tickets are Carried Forward in the month which should also be selected from the same slicer. So basically 1 single slicer should be able to show Tickets Opened, Closed & Carried Forward. I have created relationship between tables "Calendar"(Date Col) & "Tbl_INC_SR_CR_Tickets_Query" ("Opened Date").
Best Regards,
Vikram.
Solved! Go to Solution.
Hi @vikrammankar,
You need to have an active and a inactive relationship.
Then on the column that has the inactive relationship then you need to use the function USERELATIONSHIP to calculate based on the inactive relationship
Something like
Closed = CALCULATE ( COUNT( TABLE[COLUMN] ), USERELATIONSHIP(TABLE[CLOSEDDATE], CALENDAR[DATE] ) )
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @vikrammankar ,
Has refered on the e-mail you need to have both columns with the same format in this case date.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @vikrammankar,
You need to have an active and a inactive relationship.
Then on the column that has the inactive relationship then you need to use the function USERELATIONSHIP to calculate based on the inactive relationship
Something like
Closed = CALCULATE ( COUNT( TABLE[COLUMN] ), USERELATIONSHIP(TABLE[CLOSEDDATE], CALENDAR[DATE] ) )
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
I also have this issue. It works great when using the count and userelationship filter, but what I couldn't find is how to show the information on a table visual.
For example, I want to have the ability to filter dates (using the dates column from the date table), but to also show on different tables:
1. What has been opened in that date range,
2. What has been closed in that date range.
Can you please advise how to do so? thanks!
Hi @Yifatgo
Not sure if I understand the request but in whatever vvisualization you are using you must use the calendar table alongside with your metrics not the original dates.
How are you setting your tables?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi Miguel Félix! Thanks for the fast reposed, I will try to explain better:
I have a list of tickets with their creation date ("Short Date_Created", date format), some of the tickets were completed, so these also have a completion date ("Short Date_Completed", date format).
Both dates are linked to a general date table (TimeTable); The active relationship is between created date and the general date, the inactive is for the completion date (I marked them on the picture).
I'm trying to analyze the data, as a start - created vs. completed tickets.
The count was simple, as I used calculate function with count and userelationship (two measures, one per created and one per completed, with the relevant relationship). But I also need to show what was created and what was completed in the date range.
The date slicer is taken from the TimeTable, and as I can see good results for the created tickets (comparing these to the raw data that I have), I cannot find a way to present the completed tickets list (assuming it's because of the inactive relationship).
For example: I see 15 created tickets on Feb with the correct list of tickets. 12 tickets were completed on Feb, but the list doesn't filter them correctly, it just shows the created issues.
So my question is - how can I show them on a table? I want to present the "12" that pbi was able the count 😅 I hope that was clearer.
Again, thanks for taking the time to assist! @MFelix
Hi @Yifatgo ,
Believe your issue is related with the bi directionality of your filters that when you get the context filtered it return the incorrect results,
Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix,
I managed to solve it (not sure if it's elegant way, but it suits me!), wrote about it here:
Thanks again for your help!
Hi Miguel,
I just noticed. This solution is working perfectly fine for Closed Date. However it is not working for Carried Forward Date.
I have created 1 active relationship between:
"TblCalendar(Date)" & "Tbl_INC_SR_CR_Tickets_Query (Opened_Date)".
I have also created 2 inactive relationships between:
a)"TblCalendar(Date Col)" & "Tbl_INC_SR_CR_Tickets_Query (Closed_Date)"
b)"TblCalendar(Date Col)" & "Tbl_INC_SR_CR_Tickets_Query (CarriedForward_Date)"
I have created two measures:
Hi Mfelix,
Awesome......You just made my day. This is exactly what I wanted. Hats Off to U. I was struggling with this for a week. Thanks a millionnnnnnnnn....
🙂
I have one more query. Mentioning it below. Will be really very greatful, If you can help...
I have two tables,
1. Calendar
2. TblSales with 3 columns i.e. Date, Country & Sales Amount (with 3 months data in it.)
I have a clustered column chart in which I have put in Shared Axis,
"Date Hierarchy" from Calendar Table &
Country from "TblSales" Table.
In Column Values I have put Sales Amount from "TblSales" Table.
I want to sort this chart first on date as ascending and then on Sales Amount as descending. I am able to achieve this in table visual, however I am unable to achieve it in chart visual.
Mentioned below is the sample data for TblSales.
Date Country Sales Amount
01-Jan-2021 US 100
01-Jan-2021 UK 150
01-Jan-2021 UAE 200
01-Feb-2021 US 500
01-Feb-2021 UK 450
01-Feb-2021 UAE 300
01-Mar-2021 US 820
01-Mar-2021 UK 850
01-Mar-2021 UAE 700
Best Regards,
Vikram.
Hi Miguel,
I just noticed. This solution is working perfectly fine for Closed Date. However it is not working for Carried Forward Date.
I have created 1 active relationship between:
"TblCalendar(Date)" & "Tbl_INC_SR_CR_Tickets_Query (Opened_Date)".
I have also created 2 inactive relationships between:
a)"TblCalendar(Date Col)" & "Tbl_INC_SR_CR_Tickets_Query (Closed_Date)"
b)"TblCalendar(Date Col)" & "Tbl_INC_SR_CR_Tickets_Query (CarriedForward_Date)"
I have created two measures:
Hi @vikrammankar ,
Looking at the expressions all looks good.
Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.
If the information is sensitive please share it trough private message.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi Miguel,
I have made a sample file. Can you share your email id. I will send the file on it. I dont know how to upload on Google Drive or One Drive and make it available to you.
Best Regards,
Vikram.
Hi @vikrammankar ,
Has refered on the e-mail you need to have both columns with the same format in this case date.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsMarch 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
167 | |
147 | |
92 | |
70 | |
58 |