Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Userelationships with date filter

Hi guys,   Following measure works fine with an active relationship.  I need to work with an inactive relationship between Fact X and Dim_Time_startdate. How can I modify the measure so it works ...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Anonymous,

    Do you create inactive relationship between 'Fact_X'[StartdateDT] and Dim_Time_Startdate
    [Startdate  (111)]? If so, please change your DAX to the following. If the DAX doesn't work as expected, please share sample data of your table and post expected result here.

    Count Active X =

              CALCULATE(

                            COUNT('Fact_X'[StartdateX);

                            FILTER(

                                          'Fact_X';

                                          'Fact_X'[StartdateDT] <= CALCULATE(MAX(Dim_Time_Startdate
[Startdate  (111)]))

                            ); 

USERELATIONSHIP('Fact_X'[StartdateDT],Dim_Time_Startdate
[Startdate  (111)]);

                            FILTER(

                                          'Fact_X'; 'Fact_X'[EnddateDT] >= CALCULATE(MIN(Dim_Time_Startdate[Startdate (111)]))

                                          || 'Fact_X'[EnddateDT] = BLANK());

         FILTER(

                     'Dim_Ycategory';'Dim_YY'[YCode] = "WI"

         )

              )



    Regards,
    Lydia

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Anonymous,

Do you create inactive relationship between 'Fact_X'[StartdateDT] and Dim_Time_Startdate
[Startdate  (111)]? If so, please change your DAX to the following. If the DAX doesn't work as expected, please share sample data of your table and post expected result here.

Count Active X =

              CALCULATE(

                            COUNT('Fact_X'[StartdateX);

                            FILTER(

                                          'Fact_X';

                                          'Fact_X'[StartdateDT] <= CALCULATE(MAX(Dim_Time_Startdate
[Startdate  (111)]))

                            ); 

USERELATIONSHIP('Fact_X'[StartdateDT],Dim_Time_Startdate
[Startdate  (111)]);

                            FILTER(

                                          'Fact_X'; 'Fact_X'[EnddateDT] >= CALCULATE(MIN(Dim_Time_Startdate[Startdate (111)]))

                                          || 'Fact_X'[EnddateDT] = BLANK());

         FILTER(

                     'Dim_Ycategory';'Dim_YY'[YCode] = "WI"

         )

              )



Regards,
Lydia