COUNTIFS in DAX using FILTER to match dates... not working because of date formatting?

[jbaich]

Hi all, I'm trying to do a COUNTIFS type of thing with DAX via a calculated column... I'm still very, very new at this (DAX), but seems like it should be easy enough... I've tried all the different examples I've found on the internet, but no luck so far...

One of the criteria I'm trying to match is a date and it keeps throwing me errors... I've made sure that my date columns in my Data Model Tables are both formatted the same... the tables have a relationship. When I try the formulas with one criteria at a time, i get a proper count on the non-date criteria, but never with the date criteria...

The ultimate goal is something like this I think...

=CALCULATE(COUNTROWS(FILTER(ALL(rsexport),rsexport[Panel Code]=[Panel Code] && rsexport[Panel Date]=[Date] )))

This part works:

=CALCULATE(COUNTROWS(FILTER(ALL(rsexport),rsexport[Panel Code]=[Panel Code])))

This part doesn't even by itself:

=CALCULATE(COUNTROWS(FILTER(ALL(rsexport),rsexport[Panel Date]=[Date])))

So unless i have other errors in the formula above, I'm thinking i must be doing something wrong with my dates or their formats to prevent finding any matches? Dates in both tables are formatted as dd-MMM-yy

any suggestions what i might be doing wrong?

Here is a link to some sample data in Dropbox... https://www.dropbox.com/s/gow62f0t1bb6sgr/countifs.xlsx?dl=0

I've included the countifs results in the excel table so that A) my pivot table kind of works... and B) I can see them in the data model and would be able to tell if i was getting the right results.

The goal is to get the count into a calculated column or measure so that i can use slicers and have the count be dynamic...

As always, any help is very much appreciated!

Thanks

 

[citizenbh]

In table PanelDates field Date contains duplicate values

You should create a new column, you can call it PanelCode_Date as PanelCode_Date = [Panel Code] & [Date]

 

[jbaich]

so you're saying i should make a helper column that concatenates panel code and date, but then can i refer to that new value in my count formula or do i need the same helper column in my other table?

This doesn't work, but something like this for example?

=CALCULATE(COUNTROWS(FILTER(ALL(rsexport),[B]CONCATENATE(rsexport[Panel Code],rsexport[Panel Date])=[PanelCode_Date][/B])))

where [PanelCode_Date] is my new helper value?

Thanks

 

[jbaich]

hmmm... even after making the helper column PanelCode_Date in both tables, I'm still not able to get the right result from the countrows function...

=CALCULATE(COUNTROWS(FILTER(rsexport,rsexport[PanelCode_Date]=[PanelCode_Date])))

Shouldn't this be doing the trick?

[/IMG]

Thanks

 

[citizenbh]

I did not watch much, but in the fact table you have plenty of empty fields for Panel Code and Panel Date