Count number of days in a table using filters and all

[daveedd]

Hey everyone,

I have 3 tables all related:

- Case Data = the data itself

- Date = a DAX table, just a list of days for visuals

- Holiday = a list of UK Bank Holidays 

I'm trying to create a measure that shows how many of each weekday, which was not a bank holiday, there has been in the last 6 months.  And return that same value for each individual day.

AKA I need these results (code below)

 

to appear next to each weekday instead of 1.  IE every Monday should show 24, every Tuesday 25 etc

 

and I would like filters applied to interact with it.

Here's my current code, not sure line 2 is actually doing anything for me.

calculate(countrows('Date'),

     format('Case Data'[Case Created], "DDD") = format('Case Data'[Case Created], "DDD") ,

     'Date'[Last 6 calendar months] = "True",

     Holidays[Type] <> "Bank Holiday")

[v-janeyg-msft · Accepted solution]

Hi, @daveedd 

Since you didn't give a sample, we can't determine whether the columns and codes in your screenshot are columns or measures.

You can try:

CALCULATE (
    COUNTROWS ( 'Date' ),
    FILTER (
        ALL ( 'Date' ),
        'Date'[Last 6 calendar months] = "True"
            && Holidays[Type] <> "Bank Holiday"
            && 'Date'[Date of week] = SELECTEDVALUE ( 'Date'[Date of week] )
    )
)

 If it doesn't solve the problem, please provide us with what is the difference between the solution we gave you and what you want, so that we can modify it. Of course, if you can provide a sample, we will help you quickly and accurately.

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[v-janeyg-msft]

Hi, @daveedd 

Have you problem been solved? If yes, you can mark the helpful answer as solution to close the thread. If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[daveedd]

Hi, no I'm afraid the kind suggestions people have made didn't work and I still haven't managed to crack it

[Shishir22]

Can you please share pbix after hiding sensitive data?

[Shishir22]

Hello @daveedd ,

Please try creating measure something like - 

WorkingDay = 
Calculate(COUNTX(Filter(Data,[HolidayType]<>"Bank Holiday"),Data[Date]),ALLEXCEPT(Data,Data[Day of week]))

 Also, in table visual please change the date column to date from herirachy -

Please mark it as solution of it solves your requirement. Kudos are also appreciated.

Cheers,

Shishir

[johnt75]

replace the 2nd line with

REMOVEFILTERS('Date'),

[daveedd]

Afraid that just makes everything say the total number of days rather than the number of times each weekday appears

 

[johnt75]

Yes, of course. Try instead

ALLEXCEPT( 'Date'[Day of week])