Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
I have a table with the following:
- Incident ID
- Date incident opened (this is a date linking to my calendar table (dim_date))
- date incident closed
I also have a parameter field showing fields: Incidents opened and incident closed, so that an user can choose which metric to show.
How can I show on a graph the following:
- is user choses field 'Incident opened' then the graph shows the number of incidents which are opened that week / month - this one I have it is a normal graph
- if user choses fields 'Incidents closed' then the graph shows the number of incidents which closed down in that week / month.
So far the graph looks like this:
Solved! Go to Solution.
Download this example solution PBIX from Onedrive, and look at page 1 and 2
Role based date relationships.pbix
The techique is call a "role based relationship"
Role based relationships allow you to create totals on the same data by different dates.
In this example each order has an order date and despatch date.
The sales department needs to know sales ordered per month,
whereas the warehouse needs to know the value despatched per month
What was the value ordered and despatched each month?
How to do it:-
1) Create a calendar table
2) Create an order date inactive relationship
3) Create a despatch date inactive relationship
4) Create measures with USERELATIONSHIP
5) Use the calendar fields and the dax measure in the visual
Sales by order date =
CALCULATE(
SUM(Facts[Amount]),
USERELATIONSHIP('Calendar'[Date],Facts[Order date])
)
Sales by despatch date =
CALCULATE(
SUM(Facts[Amount]),
USERELATIONSHIP('Calendar'[Date],Facts[Depatched date])
)
We are unpaid volunteers and you have got free expert help which took a lot of effort.
This solution works and does exactly what you asked.
So please quickly click the [accept as solution] and the thumbs up button to leave kudos.
If you need to change or extend your request then please raise a new ticket.
You will get a quicker response and each volunteer solver will get the kudos they deserve.
One question per ticket please !
If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.
However, I will respectfully decline to help impolite lazy members twice who ask misleading or incomplete questions and then dont accept the correct soltion.
Please now click the [accept as solution] and the thumbs up button. Thank you
Hi mazwro
You are appending another question to your original question.
My solution works.
Please can you accept that solution and click the thumbs up.
The USERRELATIONSHIP by date will work ok if you try filter by Cat or add Cat as row or column or x-axis or y-axis on a visual.
Hi @mazwro ,
Thanks speedramps for the quick reply. I have some other thoughts to add:
(1) This is my test data.
calendar table = ADDCOLUMNS(CALENDAR(DATE(2024,1,1),DATE(2025,12,31)),"Year",YEAR([Date]),"Month",MONTH([Date]),"Week",WEEKNUM([Date]))
Parameter = DATATABLE (
"Slicer", STRING,
{
{ "Incident opened" },
{ "Incident closed" }
}
)
(2) We can create a measure.
Count =
VAR _open =
COUNTROWS (
FILTER (
ALLSELECTED ( 'Table' ),
YEAR ( 'Table'[Date incident opened] )
= SELECTEDVALUE ( 'calendar table'[Year] )
&& MONTH ( 'Table'[Date incident opened] )
= SELECTEDVALUE ( 'calendar table'[Month] )
&& WEEKNUM ( 'Table'[Date incident opened] )
= SELECTEDVALUE ( 'calendar table'[Week] )
)
)
VAR _close =
COUNTROWS (
FILTER (
ALLSELECTED ( 'Table' ),
YEAR ( 'Table'[Date incident closed] )
= SELECTEDVALUE ( 'calendar table'[Year] )
&& MONTH ( 'Table'[Date incident closed] )
= SELECTEDVALUE ( 'calendar table'[Month] )
&& WEEKNUM ( 'Table'[Date incident closed] )
= SELECTEDVALUE ( 'calendar table'[Week] )
)
)
RETURN
SWITCH (
SELECTEDVALUE ( 'Parameter'[Slicer] ),
"Incident opened", _open,
"Incident closed", _close
)
(3) Then the result is as follows.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Download this example solution PBIX from Onedrive, and look at page 1 and 2
Role based date relationships.pbix
The techique is call a "role based relationship"
Role based relationships allow you to create totals on the same data by different dates.
In this example each order has an order date and despatch date.
The sales department needs to know sales ordered per month,
whereas the warehouse needs to know the value despatched per month
What was the value ordered and despatched each month?
How to do it:-
1) Create a calendar table
2) Create an order date inactive relationship
3) Create a despatch date inactive relationship
4) Create measures with USERELATIONSHIP
5) Use the calendar fields and the dax measure in the visual
Sales by order date =
CALCULATE(
SUM(Facts[Amount]),
USERELATIONSHIP('Calendar'[Date],Facts[Order date])
)
Sales by despatch date =
CALCULATE(
SUM(Facts[Amount]),
USERELATIONSHIP('Calendar'[Date],Facts[Depatched date])
)
We are unpaid volunteers and you have got free expert help which took a lot of effort.
This solution works and does exactly what you asked.
So please quickly click the [accept as solution] and the thumbs up button to leave kudos.
If you need to change or extend your request then please raise a new ticket.
You will get a quicker response and each volunteer solver will get the kudos they deserve.
One question per ticket please !
If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.
However, I will respectfully decline to help impolite lazy members twice who ask misleading or incomplete questions and then dont accept the correct soltion.
Please now click the [accept as solution] and the thumbs up button. Thank you
Thank you @speedramps . I have the dales sorted out. Does the USERRELATIONSHIP work for more than one column?
To give an example of your data, if Date of Order also had grouping, such as product category, or type of product and I wanted this grouping applied as a slicer to show me products ordered in CatA and also products dispatched in CatA. How to do this?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
76 | |
63 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
64 | |
59 | |
56 |