Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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]
)
))
Solved! Go to Solution.
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 () )
Best Regards,
Dale
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 () )
Best Regards,
Dale
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.
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
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
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
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.
Best Regards,
Dale
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |