Forum Discussion
Count rows depending on a condition
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.
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!
6 Replies
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)
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 GuHello 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:
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)?
