Forum Discussion

AlejandroPCar's avatar
AlejandroPCar
Icon for Helper IV rankHelper IV
9 years ago
Solved

Unique values in a period

Hi!   I've been tying it for a while but with no positive results. I need a measure that only counts the companies that are continuosly in all the years that are in a selected period. I need be abl...
  • v-huizhn-msft's avatar
    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