Forum Discussion
Helper IBar chart: Avoiding bars without value with an condition!
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.
Community Championyou 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.
Yiyi_1989Hello there,
Thank you so much for your answer. However, I copied it but the result is now showing as following:
Would you be able to have a look? Thanks a lot!!
Brenda
- 123abc
well, plz explain your issue.
- Yiyi_1989
Thanks! So I used the following dax (based on the one you provided and did an adjustment in the end that if WeekDate >= FirstVisitDate, provide 0 instead.
The issue is that it still showed those weeks before the FirsitVisitDate as 0. I need to have them be blank(). I also checked the FiristVistDate and WeekDate separately and they seems to be correct.
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 || VisitorCount > 0, VisitorCount, 0 )