Forum Discussion
Relationship/Dax Function
I have a requirement where I have to represent revenue amount in a bar chart and Fiscal year, Quarter as Slicers.
- Revenue Amount is from Revenue table.
- Fiscal year, Quarter slicers are from Default Calendar table which is a formulated table created in Power BI.
* Revenue table has a date column Related to Default Calendar's date column ( Many to one Relationship).
Here, Fiscal year slicer and Quarter Slicer filters are not Interacting with the Revenue Amount Visual.
I tried making relationship between Fiscal years active and wrote a Dax Function
Revenue Amount = Calculate( Sum('Revenue Table'[Revenue]), USERELATIONSHIP ('Default Calendar'[Quarter],'Revenue Table'[Quarter])
This way I am able to achieve the interaction but the Revenue amount is giving the total revenue of 2 fiscal years not each seperately, How can I Achieve this ?
Hi vinoothna9 ,
The reason that cause this issue is that you diable the date relationship between two tables so that the fiscal year slicer could not work and the visual will show both fiscal years value based on your initial measure.
You can modify your measure like this:
Revenue Amount = CALCULATE ( SUM ( 'Revenue Table'[Revenue] ), FILTER ( 'Revenue Table', YEAR ( 'Revenue Table'[Date] ) = SELECTEDVALUE ( 'Default Calendar'[Fiscal year] ) ), USERELATIONSHIP ( 'Default Calendar'[Quarter], 'Revenue Table'[Quarter] ) )In my sample use the table visual to show the result directly:
Attached the sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
vinoothna9 , if date of revenue is joined with date of calendar, why to you need to join Quarter?
You should able to get fiscal year, Quarter using date table.
The information you have provided is not making the problem clear to me. Can you please explain with an example.
Appreciate your Kudos.vinoothna9 This should not need any special measures. If the relationship is already there, it will work, but relationship must be on Date column NOT quarter columns.
Revenue measure should simply look like:
Total Revenue = Sum('Revenue Table'[Revenue])
Put that in the revenue visual. Relate Revenue[Date] to Date[Date]. In slicers, use Date[FiscalQuarter] and Date[FiscalYear]
Not sure how you've created the date table and columns? https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
Yes It doesn't require any measure if Revenue[date] is related to Date[Date]. For some reason I don't see revenue Amount in the visual when I have Revenue[Date] related to Date[Date]. The slicers are working fine for other tables/columns except Revenue table.
what is the direction of the relationship with revenue table? maybe the problem is there. try to use both direcctions.
dobregon even after I change it to both it is the same.
vinoothna9 what is the date format for revenue table? Is there a match? It's probable that they are in a different format, or the date table doesn't include the dates in revenue, or something doesn't match. I find using date columns as key in relationships can cause issues at times. Try using a text DateKey column and see if that solves things, and triple check that the data matches:
https://excelwithallison.blogspot.com/2020/10/datekey-how-to-relate-your-date-data.html
https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
Hi vinoothna9
Other solution (but not good about optimization) is that you can create a calculated column in the calendar table or in your principal table and take the revenue values
Revenue = calculate(max(revenuevalue), filter(tablerevenue, tableorigin[date] = tablerevenue[date]))
And then you can do a measure with the sum of revenueHi vinoothna9 ,
The reason that cause this issue is that you diable the date relationship between two tables so that the fiscal year slicer could not work and the visual will show both fiscal years value based on your initial measure.
You can modify your measure like this:
Revenue Amount = CALCULATE ( SUM ( 'Revenue Table'[Revenue] ), FILTER ( 'Revenue Table', YEAR ( 'Revenue Table'[Date] ) = SELECTEDVALUE ( 'Default Calendar'[Fiscal year] ) ), USERELATIONSHIP ( 'Default Calendar'[Quarter], 'Revenue Table'[Quarter] ) )In my sample use the table visual to show the result directly:
Attached the sample file in the below, hopes to help you.
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
