Help with a measure: Counting rows.

[Anonymous]

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.

[v-deddai1-msft · Accepted solution]

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

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[jdbuchanan71]

@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.

[Anonymous]

This gives me the same result as the Open Matters measure unfortunately.

[jdbuchanan71]

@Anonymous 

I'm not sure what the problem is.  Can you perhaps share a deidentified version of your pbix?