Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Need help with a "CountsofDates" measure

Greetings. 

I have the following measure:

 

_AntalDage = 
CALCULATE (
    COUNTROWS ( VALUES ( 'TblHistoriske data'[Opdateringsdato] ) ),
    ALLEXCEPT ( 'TblHistoriske data', 'TblHistoriske data'[Opdateringsdato] )
)

 

 

I have two slicers in my report.

One is a date slicer, affecting the minimum and maximum values of the column Opdateringsdato (Updatedate).

The second is a hierarchy slicer, created from four columns: Enhedsnavn3, Enhedsnavn4, Enhedsnavn5, Enhedsnavn6.

 

Note that the column Updatedate is not a continuous set of dates. It measures the amount of overdue invoices in the various departments, subdepartments and so forth (The Enhedsnavnx columns) Note that Enhedsnavn3 is the highest level of the hierarchy, and Enhedsnavn6 is the lowest.

I am trying to calculate the number of dates in the Opdateringsdato column, regardless of any other filters on the table, except the dateslicer. 

I've used ChatGPT to test multiple formulas, but I keep having a measure that's affected by the hierarchy slicer.
I can't wrap my head around, what's wrong with the formula. 

Any help is appreciated.

Regards, Robert.

Need Help
  • amitchandak's avatar
    amitchandak
    2 years ago

    Anonymous , If you do not want to use filter, then use all or removefilters

     

    _AntalDage = 
CALCULATE (
    COUNTROWS ( VALUES ( 'TblHistoriske data'[Opdateringsdato] ) ),
    removefilters( 'TblHistoriske data'[Opdateringsdato] )
)

    Learn Power BI: All, allselected, removefilters,allexcept- https://www.youtube.com/watch?v=cN8AO3_vmlY&t=24270s

     

    But this will not remove filter context from row. You will only get selected dates with all values. If you need a trend of more values than selected, you need independent table

     

    Need of an Independent/disconnected Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    Anonymous , If you do not want to use filter, then use all or removefilters

     

    _AntalDage = 
CALCULATE (
    COUNTROWS ( VALUES ( 'TblHistoriske data'[Opdateringsdato] ) ),
    removefilters( 'TblHistoriske data'[Opdateringsdato] )
)

    Learn Power BI: All, allselected, removefilters,allexcept- https://www.youtube.com/watch?v=cN8AO3_vmlY&t=24270s

     

    But this will not remove filter context from row. You will only get selected dates with all values. If you need a trend of more values than selected, you need independent table

     

    Need of an Independent/disconnected Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thank you amitchandak for pointing me in the right direction. 🙂

      I ended up creating an independant table without hierarchial columns and a) count the number of filtered dates on this table and b) use it to filter my visualizations. This did the trick.