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
Anonymous
Not applicable

List of values from fact table with date range

Hi and thank you for each help. 

 

For Example:

I have Fact_table with validity period.

Id_Contract      Date_From       Date_To    .....

1                       01.01.2018      08.01.2018

2                       05.01.2018      15.01.2018

3                       10.01.2018      12.01.2018

4                       13.01.2018      22.01.2018

5                       20.01.2018      30.01.2018

 

Date_Dimension

Date_Id    Year    Month  .....

1.1.2018   2018    1

2.1.2018   2018    1

3.1.2018   2018    1

4.1.2018   2018    1

....

 

 

I want to create in PowerBI visualisation with list of contracts, but with the option of setting specific dates or periods. HOW TO DO IT??

 

So when I set up filter for example: Date_Id from 10.1.2018 to 13.1.2018 

 

Result in visualisation will be 

List_Of_Contracts 

2

3

4

 

because in selected period from 10.1.2018 to 13.1.2018 are valid only contracts with id 2,3 and 4.

 

I spend lot of time for searching, but I know only count of Contracts. In case above it is count of contracts in selected range =3.

 

Actual_Count =
CALCULATE( DISTINCTCOUNT ( Fact_Table [Id_Contract] );
           FILTER ( GENERATE ( SUMMARIZE ( Fact_Table;
                                           Fact_Table[Date_From];
                                           Fact_Table[Date_To]
                                         );
                               DATESBETWEEN ( Date_dimension[Date_Id];
                                              Fact_Table[Date_From];
                                              Fact_Table[Date_To]
                                            )
                             );
                   CONTAINS ( VALUES ( Date_dimension[Date_Id]);
                                        Date_dimension[Date_Id];
                                        Date_dimension[Date_Id]
                            )
                ))

1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee

Hi @Anonymous,

 

I would suggest you create a measure and filter the values of the measure. You can add it in the visual or add it to the Visual Level Filter.

Measure =
IF (
    MIN ( Fact_Table[Date_To] ) >= MIN ( 'Date_Dimension'[Date] )
        && MIN ( 'Fact_Table'[Date_From] ) <= MAX ( 'Date_Dimension'[Date] ),
    1,
    BLANK ()
)

List_of_values_from_fact_table_with_date_range

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-jiascu-msft
Employee
Employee

Hi @Anonymous,

 

I would suggest you create a measure and filter the values of the measure. You can add it in the visual or add it to the Visual Level Filter.

Measure =
IF (
    MIN ( Fact_Table[Date_To] ) >= MIN ( 'Date_Dimension'[Date] )
        && MIN ( 'Fact_Table'[Date_From] ) <= MAX ( 'Date_Dimension'[Date] ),
    1,
    BLANK ()
)

List_of_values_from_fact_table_with_date_range

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thank you.

I try it but it doesn't work in real situation. I attached a screen.
Before and after add Measure.

It works only my previous script for counting contracts in graph.

So in the picture are period from 1.10.2007 to 31.12.2007. So result should be 5 contracts 10563,10567,10569,12548 and 12549.

 

I make some mistake? What is wrong for list of contracts?

 

Thank you.

 

first.JPGsecond.JPG

 

 

Hi @Anonymous,

 

Which field does the slicer have? Seems it isn't [Date_ID]. It's better to use the same field in the measure "Actual_Att".

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Slicer have the same field DIM_DATE[DATE_ID] as in measure "Actual_Att".

Hi @Anonymous,

 

Are there any relationships? Can you share the file?

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

You are right. 

There si  problem with my "Data Model", specifically with relationship between DIM_DATE and FCT_CONTRACT_VALUE. There have to be obviously no relationship. When I remove this relationship it works fine, but another graph with data from  FCT_CONTRACT_VALUE is broken. 

 

My question is: I have to have one separated "Date Dimension" for FCT_CONTRACT_VALUE ane another one "Date Dimension" for FCT_CONTRACT_VALUE? So 2 identical Date Dimension tables? 

Or I have to have only inactive ralationship to one "Date Dimension"?

I hope that my question is clear.

 

Thank you

 

three.JPG

 

 

Hi @Anonymous,

 

I'm afraid so. I would suggest you create a new date table. Why? Because there are many days in the interval rather than concrete existences. For example,

from                to

2018-01-01   2018-01-31

the date 2018-01-15 is qualified. But if you create a relationship or two relationships with one is inactive, the date 2018-01-15 is ignored. Because we can't remove the impact of relationship in the visuals. 

List_of_values_from_fact_table_with_date_range

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.