topic Re: How to return 0, if criteria are not met in a measure, but for a specific period in DAX Commands and Tips

How to return 0, if criteria are not met in a measure, but for a specific period

game1 — Thu, 22 Feb 2024 18:50:58 GMT

Hello,

How to return 0, if criteria are not met in a measure, but for a specific period.

For example, I have try this, but it still not giving me the measure for the last 7 days, because I want that if for example in a specific date (Table[date]) there is not a person to be count, it must return me 0. So what it does is that it return me 0 but for all the months, not just for the last 7 days. :

VAR Last7DaysCount =
CALCULATE(
COUNT(Table[Persons]),
FILTER(
Table,
Table[Date] >= TODAY() - 6 && Table[Date] <= TODAY()
)
)
RETURN
IF(Last7DaysCount > 0, Last7DaysCount, 0)

Re: How to return 0, if criteria are not met in a measure, but for a specific period

Fowmy — Thu, 22 Feb 2024 19:05:24 GMT

game1

Not sure If I understood your question, please try:

VAR __Today = TODAY () VAR __LastSevenDays = TODAY ()-6 VAR Last7DaysCount = CALCULATE ( COUNT ( Table[Persons] ), Table[Date] >= __LastSevenDays Table[Date] <= __Today ) RETURN IF ( Last7DaysCount > 0, Last7DaysCount, 0 )

Re: How to return 0, if criteria are not met in a measure, but for a specific period

rushikale01 — Thu, 22 Feb 2024 19:41:12 GMT

Hello game1 ,

I worked with the following DAX. It worked.

Last 7 Days Count =
VAR last7Days =
FILTER ( ALL ( Person ),
Person[Date] >= TODAY () - 6 && Person[Date] <= TODAY ()
)
RETURN
IF ( CALCULATE ( COUNTROWS ( last7Days ), VALUES ( Person[Date] ) ) = BLANK (),
0,
CALCULATE ( COUNTROWS ( last7Days ), VALUES ( Person[Date] ) )
)

I created the table name "Person".

Please try this solution and let me know if any issues with it.

Thanks,

Rushika

Re: How to return 0, if criteria are not met in a measure, but for a specific period

game1 — Thu, 22 Feb 2024 19:28:23 GMT

Hello,

exactly, I have already try that, but it is not giving me for the specify period of the last 7 days. It take all the month in january, I would 0 0 0 0... but it is not the period of the last 7 days.

Re: How to return 0, if criteria are not met in a measure, but for a specific period

game1 — Thu, 22 Feb 2024 19:30:35 GMT

I just want it to return the me the period of the last 7 days and put 0 want the date is blank. But what it does is that it put 0 also for january, march,.....

Re: How to return 0, if criteria are not met in a measure, but for a specific period

game1 — Thu, 22 Feb 2024 21:33:22 GMT

I have try this, but it give me 0 for the other months and day, but I only want for the specific period of the last 7 days, so it should only be 7 dates at the axis X.

Re: How to return 0, if criteria are not met in a measure, but for a specific period

game1 — Thu, 22 Feb 2024 23:55:34 GMT

Hello,

I have add clarification ( see above).

Thanks!

Re: How to return 0, if criteria are not met in a measure, but for a specific period

rushikale01 — Fri, 23 Feb 2024 04:55:37 GMT

Hi game1 ,

In this case, if possible, when you create the line chart for last 7 days period then filter the line chart with relative date filter with respect to last 7 days. So everyday it will show you the line chart only specific with last 7 days of data only.

Re: How to return 0, if criteria are not met in a measure, but for a specific period

game1 — Fri, 23 Feb 2024 06:25:58 GMT

Yes, I have already try this and it didn't change anything.

it return me the same graphic. And if I remove

IF ( Last7DaysCount > 0, Last7DaysCount, 0 )

I can't have 0 for the blank() date. So it is not working.

Re: How to return 0, if criteria are not met in a measure, but for a specific period

Anonymous — Fri, 23 Feb 2024 08:29:18 GMT

Hi game1 ,

Base on your sample data, could you please provide your expected result? What will you want to display in the line chart? For example: Today(2024-2-23)

If there is no person from 2023-2-16 to today, it will be 0 for these dates. The other dates display the corresponding count?
If there are some person from 2023-2-16 to today(there are 2 person in your sample data in last 7 days), it display the corresponding count for all dates?

Best Regards

Re: How to return 0, if criteria are not met in a measure, but for a specific period

game1 — Fri, 23 Feb 2024 13:28:17 GMT

1-If there is no person from 2023-2-17 to today-Today(2024-2-23), it will be 0 for these dates. The other dates display the corresponding count? Yes

2-If there are some person from 2023-2-17 to today, it display it.

(there are 17(8+9) person in last 7 days for 2023-2-19.

3-And the other dates from 2023-2-17 to today, must be 0., except for 2023-2-19.

4-In this image, what is in red must not appear.

Re: How to return 0, if criteria are not met in a measure, but for a specific period

game1 — Sun, 25 Feb 2024 18:26:04 GMT

Hello,

I'm still stuck with this problem. I've tried several options, but it doesn't seem to work.

Thanks!

Re: How to return 0, if criteria are not met in a measure, but for a specific period

gmsamborn — Sun, 25 Feb 2024 19:58:51 GMT

Hi game1

After using the measure that gives you zeroes for dates outside of your range, you could add a column to your Date table.

This will include ALL dates in your table but can easily be changed.

__Include (CC) = VAR _Oldest = MINX( ALL( 'TableP'[Date] ), 'TableP'[Date] ) VAR _Newest = MAXX( ALL( 'TableP'[Date] ), 'TableP'[Date] ) VAR _Result = IF( [Date] >= _Oldest && [Date] <= _Newest, 1, 0 ) RETURN _Result

Alternatively, you could write a measure like this:

__Include (measure) = VAR _Curr = SELECTEDVALUE( 'Date'[Date] ) VAR _Oldest = MINX( ALL( 'TableP'[Date] ), 'TableP'[Date] ) VAR _Newest = MAXX( ALL( 'TableP'[Date] ), 'TableP'[Date] ) VAR _Result = IF( _Curr >= _Oldest && _Curr <= _Newest, 1, 0 ) RETURN _Result

In either case, you can filter your visual using the __Include (CC) = 1.

The calculated column is the easiest (for PBI) since it is only calculated during refresh.

Let me know if you have any questions.

Re: How to return 0, if criteria are not met in a measure, but for a specific period

gmsamborn — Sun, 25 Feb 2024 21:08:55 GMT

After re-reading (and looking at your chart), I would use a calculated column in your date table like this and use that for filtering that chart.

__Include 7 Days (CC) = IF( [Date] >= TODAY() - 6 && [Date] <= TODAY(), 1, 0 )

( My earlier post is a more generic filter that I use regularly. )

Re: How to return 0, if criteria are not met in a measure, but for a specific period

game1 — Mon, 26 Feb 2024 01:47:25 GMT

Hello,

Unfortunately, it return me the same result.

But, thanks for your help!

Re: How to return 0, if criteria are not met in a measure, but for a specific period

Anonymous — Mon, 26 Feb 2024 09:52:39 GMT

Hi game1 ,

Please update the formula of measure as below:

Measurex = VAR _seldate = SELECTEDVALUE ( 'Date'[Date] ) VAR Last7DaysCount = CALCULATE ( COUNT ( Table[Persons] ), FILTER ( Table, Table[Date] >= TODAY () - 6 && Table[Date] <= TODAY () ) ) RETURN IF ( _seldate >= TODAY () - 6 && _seldate <= TODAY (), IF ( Last7DaysCount > 0, Last7DaysCount, 0 ), BLANK () )

If the above ones can't help you get the expected result, please provide some sample data in your table (exclude sensitive data) with Text format and your expected result with special examples and screenshots.You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Re: How to return 0, if criteria are not met in a measure, but for a specific period

game1 — Tue, 27 Feb 2024 00:09:10 GMT

It gave me this graph.

But, what I want is this one:

Re: How to return 0, if criteria are not met in a measure, but for a specific period

Anonymous — Fri, 01 Mar 2024 09:00:21 GMT

Hi game1 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a date dimension table

2. Update the formula of measure as below

Measure = VAR _today = TODAY () - 4 VAR _seldate = SELECTEDVALUE ( 'Date'[Date] ) VAR Last7DaysCount = CALCULATE ( COUNT ( 'Table'[Person] ), FILTER ( 'Table', 'Table'[Date] = _seldate ) ) RETURN IF ( _seldate >= _today - 6 && _seldate <= _today, IF ( Last7DaysCount > 0, Last7DaysCount, 0 ), BLANK () )

3. Create a line chart visual (X-axis: [Date] field of date dimension table Y-axis: [Measure])

Best Regards