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
GunnerJ
Post Patron
Post Patron

Count of Dates in table 1

I'm trying to see how many records in the "Rate Sched Change" table fall between the dates of campaigns in the campaign table. My issue is that I'm getting an error starting I have duplicate dates. For the campaign table I know there are duplicate dates but there's no way around it. 

 

I know having "MAX" might be called into question but without it I get a seperate error stating "single value for column Startdate in table campaign cannot be determine..."

 

 

TOU/EV BY CAMPAIGN = 
   CALCULATE(
    DISTINCTCOUNT('Rate Sched Change'[BI_ACCT]),
       DATESBETWEEN(
           'Rate Sched Change'[BI_UPD_DT_TM],
           max(Campaign[StartDate]),
           max(Campaign[EndDate])
       )
    )

 

GunnerJ_0-1663594768388.png

 

Sample of what the end product would look like.

NameTou/EV by Campaign 
Back to school gigabit upgrade 202215
Energy Flex Plan Q1 2022221

 

 

1 ACCEPTED SOLUTION

@GunnerJ thank you for sharing! It looks like DatesBetween requires dates column, not datetime.

Please try this:

 

TOU/EV BY CAMPAIGN = 
var tmp =  CALCULATE(DISTINCTCOUNT('Rate Sched Change'[BI_ACCT]),
            'Rate Sched Change'[BI_UPD_DT_TM] >= SELECTEDVALUE(Campaign[StartDate]),
            'Rate Sched Change'[BI_UPD_DT_TM] <= SELECTEDVALUE(Campaign[EndDate])
        )
       
RETURN IF(HASONEVALUE(Campaign[Name]), tmp, BLANK())  

 

or

TOU/EV BY CAMPAIGN = 
var tmp =   CALCULATE( DISTINCTCOUNT('Rate Sched Change'[BI_ACCT]),
               DATESBETWEEN('Rate Sched Change'[BI_UPD_DT_TM].[Date], 
                SELECTEDVALUE(Campaign[StartDate]), 
                SELECTEDVALUE(Campaign[EndDate])))
       
RETURN IF(HASONEVALUE(Campaign[Name]), tmp, BLANK())  

View solution in original post

5 REPLIES 5
alena2k
Resolver IV
Resolver IV

@GunnerJ I think I understand the goal, but I am missing visualization which you are using to get an error. Maybe you can share file with sample data?

Another idea is to use calculated table:

TOU/EV BY CAMPAIGN TABLE = 
SUMMARIZE(Campaign, Campaign[name], "Rate Change Count",
    CALCULATE( DISTINCTCOUNT('Rate Sched Change'[BI_ACCT]),
                 DATESBETWEEN(
                'Rate Sched Change'[BI_UPD_DT_TM],
                SELECTEDVALUE(Campaign[StartDate]),
                SELECTEDVALUE(Campaign[EndDate])
        )
    )
)

@alena2k 

here's the sample file. 

https://www.dropbox.com/s/sg3bbw1tmn28yhp/Marketing%20Campaigns%20Dashboard%20%283%29.pbix?dl=0

 

The goal at the moment is to have a simple table layout with the campaign name next to the value of how many account were impacted. Please let me know if this helps out. 

@GunnerJ thank you for sharing! It looks like DatesBetween requires dates column, not datetime.

Please try this:

 

TOU/EV BY CAMPAIGN = 
var tmp =  CALCULATE(DISTINCTCOUNT('Rate Sched Change'[BI_ACCT]),
            'Rate Sched Change'[BI_UPD_DT_TM] >= SELECTEDVALUE(Campaign[StartDate]),
            'Rate Sched Change'[BI_UPD_DT_TM] <= SELECTEDVALUE(Campaign[EndDate])
        )
       
RETURN IF(HASONEVALUE(Campaign[Name]), tmp, BLANK())  

 

or

TOU/EV BY CAMPAIGN = 
var tmp =   CALCULATE( DISTINCTCOUNT('Rate Sched Change'[BI_ACCT]),
               DATESBETWEEN('Rate Sched Change'[BI_UPD_DT_TM].[Date], 
                SELECTEDVALUE(Campaign[StartDate]), 
                SELECTEDVALUE(Campaign[EndDate])))
       
RETURN IF(HASONEVALUE(Campaign[Name]), tmp, BLANK())  
alena2k
Resolver IV
Resolver IV

hi GunnerJ, is there any relation between Rate Sched Change and Campaign tables? 

 

If there is none, you need to create a context for the dates selection, which you did using MAX. I would use SELECTEDVALUE instead and supress table total since it does not make sense

 

TOU/EV BY CAMPAIGN = 
var tmp =    CALCULATE(
    DISTINCTCOUNT('Rate Sched Change'[BI_ACCT]),
       DATESBETWEEN(
           'Rate Sched Change'[BI_UPD_DT_TM],
           SELECTEDVALUE(Campaign[StartDate]),
           SELECTEDVALUE(Campaign[EndDate])
       )
    )
RETURN IF(HASONEVALUE(Campaign[name]), tmp, BLANK())  

@alena2k  thank you for the reply. There is no established relationship between the two tables. 

 

Unfortunantly, when using the measure provided I got the same kind of error. 

GunnerJ_0-1663600184079.png

 

Just to give additional visualization I'm hoping to determine how many accounts (2nd table) had a BI_UPD_DT_TM between the StartDate and EndDate of a given campaign name (1st table)

GunnerJ_1-1663600459302.png

 

 

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.