Forum Discussion
Unique values in a period
- 8 years ago
Hi AlejandroPCar,
For your sample table, there must relationship between company table and date table. So you should count number of selected years for each company's. Then compare it to the selected years in slicer(from begin to end), if they are equal, we count the company, otherwise we don't count it.
Because of your sample data isn't in English, I create my sample table to display the solution. There are two tables: company and date, there are relationship between them.
companydaterelationship
Then create a slicer using between mode, you don't need to create two slicers, one displays start year, another one displays end year. Create a measure to calculate the number of years selected in slicer.Selected-years = COUNTX(ALLSELECTED('Date'),'Date'[Year])
Create each company's number of years selected in slicer using the formula.experienced-year = CALCULATE(COUNT(Company[Year]),USERELATIONSHIP(Company[Year],'Date'[Year])
Check if it's equal to the number of years selected in slicer. Finally, we count the companies whose [experinced years]=[selected]. So we can use the formula below. The first is based on the two measures above. The second is a combined measure, both of them are right.Result = COUNTROWS ( FILTER ( ADDCOLUMNS ( SUMMARIZE ( Company, Company[Company], "experinced years", Company[experienced-year] ), "selected", 'Date'[Selected-years] ), [experinced years] = [selected] ) ) + 0 unique-result = COUNTROWS ( FILTER ( ADDCOLUMNS ( SUMMARIZE ( Company, Company[Company], "experinced years", CALCULATE ( COUNT ( Company[Year] ), USERELATIONSHIP ( Company[Year], 'Date'[Year] ) ) ), "selected", COUNTX ( ALLSELECTED ( 'Date' ), 'Date'[Year] ) ), [experinced years] = [selected] ) ) + 0
Then create a card to display your result.
Please download the .pbix file and check more details.
Best Regards,
Angelia
Thank you very much Angeli it seems that solved my issue, but I'm very busy now so as soon as I have enough time I will see the pbix and verify if I can do it by myself. Thanks again.
Hi AlejandroPCar,
Got it, please mark the reply as answer if you have resolved your issue.
Best Regards,
Angelia
- AlejandroPCar8 years ago
Helper IV
Hi Angelia! v-huizhn-msft
Sorry about the time for a response. Your solution works perfectly. Well... in fact I did not use all your formulas and neither the full proccess because my situation is a little bit more complex but it helped me to understand the measures and so your help was HUGE.
Again thanks a lot.