Forum Discussion

android1's avatar
android1
Icon for Post Patron rankPost Patron
8 years ago
Solved

Count if Date is earlier than selected date

Hi,

 

I am trying to count the distinct number of CarerRef if the StartDate is before a selected date in a slicer (this slicer gets its date from DateKey table). Only count CarerRef if DateLeft is blank.

 

Using: Active Point In Time = CALCULATE(DISTINCTCOUNT(vw_CrystalCarerAudit[CarerRef]),vw_CrystalCarerAudit[DateLeft] <> BLANK(),vw_CrystalCarerAudit[StartDate] <= RELATEDTABLE(DateKey),DateKey[Date])

 

but getting scalar value error: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

 

What am I doing wrong here?

 

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi,
    Give this a shot

     

    Active Point In Time =
VAR selecteddate =
    SELECTEDVALUE ( DateKey[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( vw_CrystalCarerAudit[CarerRef] ),
        vw_CrystalCarerAudit[DateLeft] <> BLANK (),
        vw_CrystalCarerAudit[StartDate] <= selecteddate
    )


     

7 Replies

  • liu's avatar
    liu
    Regular Visitor
    8 years ago

    Try:

     

    Active Point In Time = CALCULATE(

         DISTINCTCOUNT(vw_CrystalCarerAudit[CarerRef]),

         FILTER(vw_CrystalCarerAudit[DateLeft] <> BLANK(),

                    vw_CrystalCarerAudit[StartDate] <= RELATEDTABLE(DateKey),DateKey[Date]))

    • android1's avatar
      android1
      Icon for Post Patron rankPost Patron
      8 years ago

      liu

       

      Doesn't like the 2 filters. It's giving me 'Too many arguments were passed to the FILTER function. The maximum argument count for the function is 2.'

      • liu's avatar
        liu
        Regular Visitor
        8 years ago

        Try this instead:

         

        Active Point In Time = CALCULATE

        (

             DISTINCTCOUNT(vw_CrystalCarerAudit[CarerRef]),

             FILTER(vw_CrystalCarerAudit,

                        vw_CrystalCarerAudit[DateLeft] <> BLANK() &&

                        vw_CrystalCarerAudit[StartDate] <= RELATEDTABLE(DateKey),DateKey[Date])

        )