Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |