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 measure that was created to count the number of bugs in a range of dates on another table based on when the bug was opened and closed. The measure is:
Sev3 = SUMX( 'Combined', COUNTX( FILTERS( 'DatesLink'[Date] ), IF( ( [Date] >= Combined[CreatedDate] && [Date] <= TODAY() ) && ( Combined[CompletedDate]=BLANK() || [Date] < Combined[ClosedDate] ) && Combined[Severity] = "3 - Medium", 1, BLANK() ) ) )
When I put the dates from DatesLink[Date] in a table, it is sequential (left picture), however, when I attempt to apply that measure to the same data, it skips dates (right picture). Can anyone point me to why this might be happening?
Solved! Go to Solution.
@Anonymous it means your measure is returning blank value. You can drop down date column and click show items with no data and it will show all the dates even if measure is returning blank value.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hey @Anonymous ,
I'm wondering if the tables 'Combined' and 'DatesLink' are related, and if this is the case what is this relationship? My assumption: the relationship is between the columns 'DatesLink'[Date] (on the one side) and 'Combined'[CreatedDate].
Maybe you might consider to check the option "Show items with no data" for the column 'DatesLink'[Date] that you are using in the table visual, the next screenshot shows how to check/uncheck this option:
I'm also wondering, why you are using the function FILTERS, from my understanding of the formula you provided I would use VALUES instead, even if both would return the same "table", as 'DatesLink'[Date] is used on the rows.
I would rewrite the measure like so (no idea if this solves the issue with skipped dates), but at least it chaches TODAY into a variable.
Sev3 =
var _today = [today]
return
SUMX(
'Combined'
, COUNTX(
VALUES( 'DatesLink'[Date] )
, IF(
AND(
AND(
AND( [Date] >= Combined[CreatedDate] , [Date] <= _today )
, OR ( Combined[CompletedDate]=BLANK() , [Date] < Combined[ClosedDate] )
)
, Combined[Severity] = "3 - Medium"
)
, 1, BLANK()
)
)
)
Maybe the reason why dates are "skipped" if the measure is applied is simply this: it returns BLANK() for the dates from 2016 and 2017, as the bugs created in 2016 are no longer open (hopefully) 🙂
Regards,
Tom
@Anonymous it means your measure is returning blank value. You can drop down date column and click show items with no data and it will show all the dates even if measure is returning blank value.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |