Forum Discussion
Help with a measure: Counting rows.
Hello,
I need some halp with a simple DAX query.
I have a table of facts that contains, amongst other things, a created date column ([credte]), a closed date column ([clomatdte]) and a fee earner code ([solcde])
In addition to the fact table (lgmattrr) I have a date table and a fee earners table (lgsoltrr). On the report page there are slicers to filter the fee earner table and filter the date. The date table is related to [credte] (created date)
In my visual I need to count the rows where there is no closed date (the matter is still open). I need a measure that shows the number of matters opened within the selected date range that have no closed date. This is easy:
Open Matters =
COUNTROWS(
FILTER(
lgmattrr,
ISBLANK( lgmattrr[clomatdte] ) )
)
I also need one to show the all-time open matters per fee earner, so I need the measure to ignore the date filter but include the filter for [solcde].
I've tried a few things using ALL() and ALLEXCEPT() but they always show the same number across all fee earners.
As a workaround I referenced the facts table in Power Query, filtered by clomatdte = null dropped all the columns except fee earner and related that to the fee earner table, then do a simple countrows() across that. Seems like a bit of a kludge though, I'd love to know how to do this in DAX without creating new tables in the model, if possible.
Hi Anonymous ,
Would you please try to use the following measure:
Open Matters = CALCULATE ( COUNTROWS ( lgmattrr ), FILTER ( lgmattrr, ISBLANK ( lgmattrr[clomatdte] ) ), ALL ( 'Date Table' ) )If this post help, please consider accept it as the solution to help other member find it more quickly.
Best Regards,
Dedmon Dai
5 Replies
- jdbuchanan71Super User
Anonymous
Does this give you what you are looking for?
All Time Open = CALCULATE ( [Open Matters], ALL ( Date ) )This simply calculates your [Open Matters] measure as if there were no filters on the date table.
- AnonymousNot applicable
This gives me the same result as the Open Matters measure unfortunately.
- jdbuchanan71Super User
Anonymous
I'm not sure what the problem is. Can you perhaps share a deidentified version of your pbix?
- Ashish_MathurSuper User
Hi,
Share some data and show the expected result.
- v-deddai1-msftCommunity Support
Hi Anonymous ,
Would you please try to use the following measure:
Open Matters = CALCULATE ( COUNTROWS ( lgmattrr ), FILTER ( lgmattrr, ISBLANK ( lgmattrr[clomatdte] ) ), ALL ( 'Date Table' ) )If this post help, please consider accept it as the solution to help other member find it more quickly.
Best Regards,
Dedmon Dai