I've had a report based on Support Ticket data that used multiple tables due to different categories of tickets we have. Recently, I learned how to merge them into one "master" table using SQL and Merge in Power BI. Now I'm rebuilding my report, page by page. I've hit a roadblock when attempting to recreate the comparison of ticket volume this year versus last year (working model only uses my "IncidentRequests" and "DimDate" tables). Using Sam McKay's YouTube method of calculated measures worked in the first .pbix report, but fails on the second, and I cannot tell why.
The first measure counts the total tickets:
Total Tickets = COUNTROWS( IncidentsRequests )
The second measure counts just the tickets from the previous year (system went live Feb 2016):
Total Tickets LY = CALCULATE( [Total Tickets], SAMEPERIODLASTYEAR( 'Dim Date'[Date] ) )
The final measure found the difference between the two:
Diff. Between TY and LY = IF( ISBLANK( [Total Tickets LY] ), BLANK(), [Total Tickets] - [Total Tickets LY] )
The results in a simple table gave me what I wanted:
Note how 2016 monthly ticket counts are now repeated next to the same 2017 month? That's the expected outcome.
Now in the NEW report, the new "Master" table is the data table used for this, against a copy of the same DimDate table, same main relationship between. I've copied/pasted the previous measures (adding a "version # prefix to troubleshoot different calcs), but getting different results in the table:
The total at the bottom in "1TotalTickets" column is accurate, as is the Total in the next "1TotalTicketsLY" column... but there's no data in the column above it... no monthly breakdown. (remember, numbers are higher due to merged ticket tables) Trying different measures in columns further to the right gets me accurate counts of the tickets from each different year, but does not help me compare them on a "same month" basis. Other measure attempts are failures, giving empty columns
I can't think of any fundamental differences between the two reports' structure, though I do have a second relationship drawn between the Master and Date tables for closed ticket date reporting. (can't be caused by that, right?) So what's causing the copied/pasted measures to behave differently in the new report? Most importantly, I want to be able to create the "month-against-month" dimension. What am I missing here?
Thanks in advance for any and all help/advice!
1. Which table are the fields [Year] and [Month] of the visual from? I afraid they are from the table "IncidentsRequests".
2. Which relationship is active as you say there are two relationships?
The two above are the possible causes. Please share the pbix file if the issue is still there. A dummy one is enough.
Hi, thanks for the response! Answers as follows:
1. The [Year] and [Month] fields are from the "Dim Date" table, the calendar table I use for every pbix report, including the previous report I'm trying to repeat the success of.
2. The active relationship is drawn exactly the same as the previous report. Here is the old report (measure is working)...
... and here is the new report (measure is NOT working)...
The relationships are duplicated exactly, aside from the inactive secondary relationship. (hoping THAT isn't the problem)
Unfortunately I'm unable to share the pbix file, as it contains private health information in the data tables, and I'm afraid creating a dummy file would not properly recreate the environment I'm having the issue in. Hopefully my answers and pictures are enough to further the discussion. Thanks in advance!
Please make a little change. Don't create a relationship using the Datekey, use the Date directly (the Date type columns). This could be the workaround. I will find out the root cause later and share it here.
Hi, @v-jiascu-msft - thanks for replying.
Could that really be the issue? I only ask because on the first pbix (where this whole set of DAX calculations works properly) the relationship is set between the Datekey columns in each table, which read "Whole Number" in the Data Type formatting section. If it works in the first pbix, then logic says the same thing should work in the rebuild with the same "Whole Number" setting... right? Am I missing something? Thanks in advance for any help!
The datekey should work. But it messed up in my test. Did changing the relationship solve your issue? If it can work, we can take it as a workaround for now. I'm still trying to find out the root cause.
Did it work when you had the relationship between date columns.
Hi @v-jiascu-msft, thanks for keeping up on this!
I have had something unusual happen. When I loaded the rebuild pbix file (the new one that didn't work like the old one), I suddenly noticed the calculations were working! I didn't touch the DAX formulas, the relationships, or anything. There was a refresh of the underlying SQL Server data, and some other DAX calculated columns added for unrelated reporting... that's it. No relationship changes, it simply started to work.
I'm not entirely sure I can still call this "broken". I do have to say that the sudden change is quite a mystery. I can't think of what would have changed this. I did install the November update in the middle of all this, so maybe that had something to do with it? Thoughts anyone?
I tried to find out the mode of this issue. But it is intermittent. So I asked you about this. I'm glad you can go on now. I will appreciate it if you can close this thread. You can post a new one anytime you encounter an issue.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.