Count entries in custom table

[Anonymous]

Hallo All,

I am trying to count the number of entries on a Date table column, but it should be dynamic in the sense that I am using 3 fields from the date table namely, year, month and day. The point is, if I drill the graph to year it should count the number of years over allselected, month the number of months and days the number of days. The measure I am currently trying to do this with is as follows;

CALCULATE(COUNTX(SUMMARIZE('Date' , "MaxDate" , MAX('Date'[Date])) , [MaxDate]) , ALLSELECTED('Date'))

My reasoning is this, the summarize table returns the max date over a field so, if I am over year it will return the max date for every year, over month the max date for every month and over day it will return every day due to row context. Meaning, if I have a range selected over 5 years it will return 5 max days and should count 5, over month it should return 60 different max days and should therefore count 60 and for days the amount of days.

The problem is, the measure behaves the same whether I put it in the calculate environment or not. It always just returns 1 for each row. Can someone explain to me what the dax does here that it just ignores the allselected environment?

[v-shex-msft]

Hi @Anonymous,

I'd like to suggest you take a look at below blog about how to handle the different hierarchy levels:

Clever Hierarchy Handling in DAX 

Regards,

Xiaoxin Sheng

[Anonymous]

Thank you for this additional info. I will look at it also.

[amitchandak · Accepted solution]

@Anonymous , I think it should some role of what if selected

some thing like this

measure =
var _Expression=if(ISFILTERED('Date'[Month Year]),values(Date[Month-year]),if(ISFILTERED('Date'[Year]),Values(Date[Year]),Values(Date[Date])))
return
countx(_Expression,Date[Date])

Also, other examples where measures have been filtered

https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

[Anonymous]

So I already tried something similar where I calculate the count of years, months and days in separate variables and return it in such an isfiltered IF statement, similar to yours and that did not work. But, you second link made me understand something about hasonefilter and the same measure as above works very will with hasonefilter. Thank you for your help.