Count if date field have corresponding value

[PBIX_COACH]

Hello Team I need some help with a Dax solution. I am trying to get test 1 measure to indicate if column b has at least 1 value in it and if not it should remain blank or 0. once I have that I should be able to then divide Test 1 results by the weeks in the period column to get an Engagement %. 

Current formula (not working)

Test1 = COUNTROWS (

    FILTER (

        VALUES ( CalendarDim[Start of Week]),

        CALCULATE ( COUNT ( '03 Contact '[B])>1 )

    )

)

Any assistance is truly appreciated

[PBIX_COACH]

Any help is appreciated I think it's a simple solution but I'm missing something simple

[FreemanZ]

hi @PBIX_COACH 

your screenshot seems like a table visual. could provide some raw dataset?

[SamInogic]

Hi @PBIX_COACH ,

As per our understanding you are looking for a measure which will have following DAX Expression:

Test 1 = IF(COUNT('sample Table'[B])>=1,1,0)

These results as you said, if Column [B] doesn't have any value so measure should be blank or 0,

After this you also want to Divide result by the [Week in period], For this we can create a Measure which will store the Value of [Week in period]

weeks in period_Messure = max('sample Table'[Week in period])

After this Use below DAX Expression for Divide,

Weekly Engagement % = DIVIDE([Test 1],[weeks in period_Messure],1)

If this answer helps, please mark it as Accepted Solution so it would help others to find the solution.

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

[PBIX_COACH]

hi @SamInogic the subtotals are incorrect if you remove the start of the week from the table it is not counting correctly and the % calculation is incorrect.