Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a table of issues that are tagged with a date so I can show the number of issues over time, but I also want to show the number of new issues per day. I thougt I could do this using a measure with EXCEPT, where i would just compare the list of issues from the current day to the list from teh previous day and use COUNTROWS to display the differences. I am, however, having trouble getting the previous day table to return anything but blank in the EXCPET formula. I attached some screenshots/code. To clarify, the number being returned by the Test measure is just the count of issues for the current day, since the prevday table is empty. Any help would be appreciated!
Test = VAR prevday = DISTINCT ( SELECTCOLUMNS ( FILTER ( IssueTracking, IssueTracking[IssueDate] = [SelectedDatePrevDay] ), "Issue", IssueTracking[Issue] ) ) VAR currday = DISTINCT ( SELECTCOLUMNS ( FILTER ( IssueTracking, IssueTracking[IssueDate] = [SelectedDate] ), "Issue", IssueTracking[Issue] ) ) VAR compare = EXCEPT ( currday, prevday ) VAR difference = COUNTROWS ( compare ) RETURN IF ( [SelectedDate] = [MinIssueDate], BLANK (), difference )
Test2 = VAR prevday = DISTINCT ( SELECTCOLUMNS ( FILTER ( IssueTracking, IssueTracking[IssueDate] = [SelectedDatePrevDay]), "Issue", IssueTracking[Issue] ) ) var difference = COUNTROWS( prevday ) RETURN difference
SelectedDate = FIRSTNONBLANK(Dates[Date], TRUE())
SelectedDatePrevDay = FIRSTNONBLANK(Dates[Date], TRUE()) - 1
Hi @doubles
Try to remove the bidirectionality from your relationship between the dates table and the IssueTracking table.
If doesnt' work can you share a sample of the data?
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português@MFelix -- Thank you for the reply, changing the cardinality didn't work. I put some sample data at the link below.
https://drive.google.com/open?id=1JpbzQsfDl81FoSD9bvAUGuQus0NE4Hhb
I solved this using calcualted tables, however would still be curious to see if anyone knows how to do it with a measure.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |