URGENT - dax function not working

[xiumi_hou]

Hi all,

I have the month slicer, I was trying to calculate how many people activated at the begining of the seleced month. For example: when I selected April, I want to know how many people was used to be actived before or on the April 1st. However, the dax function return blank: each parent id represents 1 people

Activation before = CALCULATE(DISTINCTCOUNT(f_calls[Parent_id]),filter(f_calls,

f_calls[f_Cases.Activation_date_c]<min(d_date[Date])

),USERELATIONSHIP(f_calls[f_Cases.Activation_date_c],d_date[Date]))

Thanks a lot!

[Jihwan_Kim]

Hi, @xiumi_hou 

can you try the below?

Activation before =
CALCULATE (
DISTINCTCOUNT ( f_calls[Parent_id] ),
FILTER ( f_calls, f_calls[f_Cases.Activation_date_c] = MIN ( d_date[Date] ) ),
USERELATIONSHIP ( f_calls[f_Cases.Activation_date_c], d_date[Date] )
)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

[xiumi_hou]

Thanks for the reply. It still shows blank. I want to calculate how many was active before or on the April 1st

[Jihwan_Kim]

Hi, @xiumi_hou 

Oh... before or on the 1st day of the selected month??

Or is it before the 1st day?

I hope you can change as you wish.

please include ALLSELECTED function inside the measure.

Activation before =
CALCULATE (
    DISTINCTCOUNT ( f_calls[Parent_id] ),
    FILTER (
        ALLSELECTED ( f_calls ),
        f_calls[f_Cases.Activation_date_c] <= MIN ( d_date[Date] )
    ),
    USERELATIONSHIP ( f_calls[f_Cases.Activation_date_c], d_date[Date] )
)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

[xiumi_hou]

Thanks for the reply. This still show results blank. Can you please help me take a look at the below query?

Activation before = CALCULATE(COUNT(d_Cases[Id]),filter(ALLSELECTED(d_Cases),

d_Cases[Activation_date_c]<=min(d_date[Column_date])

),USERELATIONSHIP(d_Cases[Activation_date_c],d_date[Date]))

[Jihwan_Kim]

Hi,

I am not sure, but perhaps, it has something to do with the context.

Please let me have a look at your sample pbix file. Then I can try to create more accurate measures.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

[xiumi_hou]

Thanks Jihwan, I have described it as below as attahced a sample dataset, can you please help me and take a look?

[Jihwan_Kim]

Hi, @xiumi_hou 

Thank you for your message.

In this case, I think the approach has to be made in a little bit different way.

I can write based on this case only. My measure might not be appropriate if there are any other related tables to this case.

- do not activate any relationships between date-table and case-table. All relationships have to be inactive.

Please check the below if I correctly understand your request, and please let me know.

Active Clients Count =

COUNTROWS(CALCULATETABLE('Cases', FILTER( 'Cases',

'Cases'[Activation Date] <= MAX(dates[Date]) && 'Cases'[Closed Date] >= MIN( dates[Date]) && 'Cases'[Activation Date] <> BLANK())))

+

COUNTROWS( CALCULATETABLE('Cases', FILTER( 'Cases',

'Cases'[Activation Date] <= MAX(dates[Date] )&& 'Cases'[Closed Date] =BLANK() && 'Cases'[Activation Date] <> BLANK())))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

[xiumi_hou]

Thanks @Jihwan_Kim  The below query can return the result. The only thing is I have active relationship built for case table(assigned date) with the date table (date). - This can not be removed since a lots of other querys are based on this.  Do I need to made any changes? 

[Jihwan_Kim]

Hi, @xiumi_hou 

Oh, in that case,

allselected or all function can help to remove filters in only that particular DAX measure.

[Angith_Nair]

Hi @xiumi_hou ,

Try with this code..

Activation before =
CALCULATE (
    DISTINCTCOUNT ( d_Cases[Id] ),
    d_Cases[Activation_date_c] <= MIN ( d_date[Column_date] ),
    CROSSFILTER ( 'case table'[assigned date], d_date[Date], NONE ),
    USERELATIONSHIP ( d_Cases[Activation_date_c], d_date[Date] )
)