topic Bar chart: Avoiding bars without value with an condition! in DAX Commands and Tips

Bar chart: Avoiding bars without value with an condition!

Yiyi_1989 — Thu, 26 Oct 2023 10:47:47 GMT

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 ❤️

Re: Bar chart: Avoiding bars without value with an condition!

123abc — Thu, 26 Oct 2023 11:00:30 GMT

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.

Re: Bar chart: Avoiding bars without value with an condition!

Yiyi_1989 — Thu, 26 Oct 2023 11:23:37 GMT

Hello 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

Re: Bar chart: Avoiding bars without value with an condition!

123abc — Thu, 26 Oct 2023 11:29:41 GMT

well, plz explain your issue.

Re: Bar chart: Avoiding bars without value with an condition!

Yiyi_1989 — Thu, 26 Oct 2023 12:10:30 GMT

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 )

Re: Bar chart: Avoiding bars without value with an condition!

123abc — Thu, 26 Oct 2023 12:26:24 GMT

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.

Re: Bar chart: Avoiding bars without value with an condition!

Yiyi_1989 — Fri, 27 Oct 2023 06:13:10 GMT

Hello,

Great, now it works! Thank you so much for your help ❤️ ❤️ <3. People like you make the community such a valuable place.

Re: Bar chart: Avoiding bars without value with an condition!

123abc — Fri, 27 Oct 2023 06:33:49 GMT

You are always wellcome.😊