Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
android1
Post Patron
Post Patron

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?

 

2018-04-05 19_29_11-Untitled - Power BI Desktop.png

1 ACCEPTED SOLUTION

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
    )


 


Regards
Zubair

Please try my custom visuals

View solution in original post

7 REPLIES 7
liu
Regular Visitor

Try:

 

Active Point In Time = CALCULATE(

     DISTINCTCOUNT(vw_CrystalCarerAudit[CarerRef]),

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

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

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

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])

)

Still not liking that ->

 

2018-04-05 20_48_48-Untitled - Power BI Desktop.png

Hmm... I've yet to have to use RELATEDTABLE in a FILTER argument, but it seemed like because of the comma, it's assuming DateKey[Date] is a 3rd argument of FILTER, and not just part of RELATEDTABLE.

 

Perhaps try:

1. Put parenthesis around RELATEDTABLE(DateKey),DateKey[Date]

or

2. Drop the RELATEDTABLE(DateKey), and just use DateKey[Date].

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
    )


 


Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad That works great. Thank you very much.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.