Count rows depending on a condition

[SuperSayan]

Hello everyone,

I have an issue that I can't figure out.

I'm trying to count the numbers of lines in a table where there is no date (actually value is 0 so the date is 01/01/1900) and then I'm displaying that on a graph depending on other columns.

My DAX expression is :

RestantaFaire = COUNTROWS(FILTER(HistoriqueInter;HistoriqueInter[FinréelleDateH].[Année]=1900)) 

Which doesn't work!

However, if I create a new table=

Restant = FILTER(HistoriqueInter;HistoriqueInter[FinréelleDateH].[Année]=1900)

And then I create a new measure:

RestantaFaire= COUNTROWS(Restant)

Then it works! But I find it very silly to create a full table (25 000 lines) just to countrows of this new table on which i also need to create the same relationship as the "HistoriqueInter" to be able to display the same info.

[v-yulgu-msft]

Hi @SuperSayan,

Based on my test, such a DAX formula: RestantaFaire = COUNTROWS(FILTER(HistoriqueInter;HistoriqueInter[FinréelleDateH].[Année]=1900))  could return correct result.

What did you mean "it doesn't work"? Does it prompt any error? Or it returned wrong value? Please re-create the measure for a test. If possible, could you share your pbix file for further analysis?

Regards,
Yuliana Gu

[SuperSayan]

Hello @v-yulgu-msft and @Greg_Deckler,

Thanks to both of you for your ideas.

Indeed I fell into the "it doesn't work" non constructive complain.

What I meant is that I don't get any error message but when I want to use the measure, it seems to be blank (same results with the use of VAR proposed by smoupre).

RestantaFaire = COUNTROWS(FILTER(HistoriqueInter;HistoriqueInter[FinréelleDateH].[Année]=1900))

RestantaFaire2 =

VAR Restant = FILTER(HistoriqueInter;HistoriqueInter[FinréelleDateH].[Année]=1900)

RETURN COUNTROWS(Restant)

ouvert = COUNTROWS('Restant')

where Restant is a calculated table Restant = FILTER(HistoriqueInter;HistoriqueInter[FinréelleDateH].[Année]=1900)

I still can't figure out why I'm not getting any value from the 2 measures and it works for the third version...

At the end of the day, I'm able to do what I need but it is so inefficient that it bothers me a lot.

Simple table with the 3 measures:

[SuperSayan]

I just realised something that may be the cause of this.
My table "HistoriqueInter" is not a simple table, it is a table created by appending 2 tables, the first table have the interventions of 2016 (table called "Inter 2016") and the second one the ones from 2017 (very logically called "Inter 2017").

So I did a test and if I'm using the exact same measure on a "simple" table, in this example my  table "Inter 2017" then it behaves as planned.

My question is, is there a way to make it work on a table constructed by appending 2 tables or do I need to go back to the original tables (and therefore still need to duplicate my tables relationships)?

[v-yulgu-msft]

Hi @SuperSayan,

Based on my test, below formula works for append table, too.

RestantaFaire = COUNTROWS(FILTER(HistoriqueInter;HistoriqueInter[FinréelleDateH].[Année]=1900))

How did you append the two tables? In my test, I appended them in Query Editor mode using the "Append queries as new" option.

Regards,
Yuliana Gu

[SuperSayan · Accepted solution]

Hi @v-yulgu-msft and @Greg_Deckler

I really have to apologies to both of you! 

I just realized where this issue comes from... I had put a filter on the whole report that was filtering all lines with dates <2000 (therefore including the 1900) which resulted in not showing anything in my measure.

I removed the the filter and now it works.

I feel a bit stupid after that....

Thanks again for taking the time to help on a "non existing" issue!

[Greg_Deckler]

Hmm, I was able to get COUNTROWS to work in that manner using a FILTER function to return a table. Not sure what is going wrong on your end. That being said, you could also do it this way:

RestantaFaire = 

VAR Restant = FILTER(HistoriqueInter;HistoriqueInter[FinréelleDateH].[Année]=1900)

RETURN COUNTROWS(Restant)