Forum Discussion
Bar chart: Avoiding bars without value with an condition!
Hello kind people,
I am creating a bar chart which represents the number of visitors per week. I want to keep those bars with 0 visitor in the chart, but only in those weeks after the original week when they have visited! Please see the graph below what I mean:
I tried with following DAX dreaming to fix the issue, but not working. Power BI had error message that "the search text provided to function "find" could not be found in the given text" even though I didn't even use Find function :O!
Visitors =
VAR FirstVisitDate = CALCULATE ( MIN ( 'Usage_log'[timestamp].[Date] ), 'Usage_log' )
VAR WeekDate = SELECTEDVALUE( DateTable[StartDate] )
VAR visitor = CALCULATE (
DISTINCTCOUNT(Usage_log[user_Id]),
ALL(Usage_log),
KEEPFILTERS(ALL('User_Categorization_Revised'[User_Type]))
)
RETURN
IF (
WeekDate < FirstVisitDate,
BLANK(),
IF(ISBLANK(visitor) || visitor = 0, 0, visitor)
)
Thanks a lot in advance if you could help ❤️
Now I understand that you want to show blanks for weeks before the FirstVisitDate. If your current formula is still showing 0 for those weeks, you should make sure to adjust the formula accordingly. It seems like there might be an issue with the logic. You can update your DAX formula to achieve the desired result:
Visitors =
VAR FirstVisitDate = CALCULATE(MIN('Usage_log'[Date]), ALL('Usage_log'))
VAR WeekDate = SELECTEDVALUE(DateTable[StartDate])
VAR VisitorCount =
CALCULATE(
DISTINCTCOUNT(Usage_log[user_Id]),
ALL(Usage_log),
KEEPFILTERS(ALL('User_Categorization_Revised'[User_Type]))
)
RETURN
IF (
WeekDate < FirstVisitDate,
BLANK(),
IF(VisitorCount > 0, VisitorCount, BLANK())
)In this modified formula, it explicitly returns BLANK() when WeekDate < FirstVisitDate, ensuring that weeks before the first visit date are left blank. Additionally, it checks for VisitorCount > 0 to decide whether to show the actual visitor count or a blank.
Please make sure that your DateTable and 'Usage_log' table have the appropriate relationships and that the data types of 'Date' and 'StartDate' columns match for the comparison to work correctly.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
7 Replies
you want to create a bar chart in Power BI that shows the number of visitors per week, but you want to exclude weeks with 0 visitors unless those weeks come after the first week with visitors. Your DAX expression seems to be on the right track, but there are some issues in your code that need to be addressed.
Here's a revised version of your DAX formula:
Visitors =
VAR FirstVisitDate = CALCULATE(MIN('Usage_log'[timestamp].[Date]), ALL('Usage_log'))
VAR WeekDate = SELECTEDVALUE(DateTable[StartDate])
VAR VisitorCount =
CALCULATE(
DISTINCTCOUNT(Usage_log[user_Id]),
ALL(Usage_log),
KEEPFILTERS(ALL('User_Categorization_Revised'[User_Type]))
)
RETURN
IF (
WeekDate < FirstVisitDate || VisitorCount > 0,
VisitorCount,
BLANK()
)Here are the changes I made to your code:
I adjusted the FirstVisitDate calculation to consider all rows in the 'Usage_log' table by using ALL('Usage_log').
I changed the condition in the IF statement. If WeekDate is less than FirstVisitDate, it checks if VisitorCount is greater than 0, and if so, it displays the visitor count; otherwise, it returns BLANK().
This DAX formula should give you the desired result. It will show bars for weeks with 0 visitors only if they come after the first week with visitors.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
