Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

  • 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's avatar
      Anonymous
      Not applicable

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

       

      • jdbuchanan71's avatar
        jdbuchanan71
        Super User

        Anonymous 

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

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community 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