Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi,
I have 3 measures say, A, B, and C and I want to create a bar chart visual where the y-axis = (A+B+C)/3, and the x-axis is date as Year-Quarter. Something like this:
However, all A, B, and C are based on different date columns as follow:
- A uses the columns Table1[Year] and Table1[Quarter]
- B uses the columns Table1[Closed Date]
- C uses the columns DateTable1[Date] which has a relationship with Table1[Date]
These date columns are all different so the measures can't be calculated using the same date column.
What is the most simple way to create this graph?
Thank you.
Hi,
The most simple way would be to make to adjust the model so that they all reference the same date column. For example, you could to Table 1 add a calculated column that returns the last date of each quarter based on year and quarter (e.g. 2022 and Qtr 1 becomes 31/3/2022 if that's your fiscal year) and then create a relationship between that column and the date column in the date table. And then something similar for column B.
Another approach could be to create a calculated table that you group in common date periods, using for example SUMMARIZE(), so that A, B, C turn out in the same table with the same date column.
Hi @TomasAndersson ,
Thank you for your reply!
For the first solution, the newly added relationships will have to be inactive relationships, as measure A, B, and C all use a date column in the same table? - and I thought that visuals don't use inactive relationships?
Hi, sorry missed that they were all Table 1.
I'm a bit unsure of what your model looks like. What date is Table1[Year] and Table1[Quarter]? Table1[Date]? And Table1[Date] has a relationship to your DateTable? If so, they should work fine together if you use the year and quarter columns from the DateTable to summarize.
For [ClosingDate], you'd have to create an inactive relationship to the date table and then use
Hi @TomasAndersson,
To summarise, the data is an historical view on work items. Table1[Year] and Table1[PI] refers to the quarter the work item is allocated to be completed in. Table1[Date] refers to the date this version of the work item was recorded. Table1[Closed Date] is the date that the item is completed.
Another question I have about using USERELATIONSHIP. I am not sure how to implement this as the measures A,B, and C don't use the respective date columns in their calculation. The date columns only come into play when I plot these measures on separate visuals (e.g. y axis is measure A, and x axis is the date column measure A uses.)
As an example, one of the measures is as follow, the others are similar:
Hi @TomasAndersson ,
I have tried wrapping the finalValue in a calculate function with userelationship as such:
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
105 | |
99 | |
99 | |
38 | |
37 |
User | Count |
---|---|
157 | |
120 | |
74 | |
72 | |
63 |