Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |