Forum Discussion

mayurwadhwani's avatar
mayurwadhwani
Helper I
5 years ago
Solved

DAX Measure for Date Problem

Hello Experts, I have a table named Members and below is the data: I want to get distinct count of pkey year wise. If a pkey is active from 2019 to 9999 or till 2019 or greater than 2019 th...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi mayurwadhwani ,

    I updated the formula in the original sample pbix file, please check whether it is ok.

     

    Measure = 
VAR _count =
    COUNTROWS ( ALLSELECTED ( 'DateDim'[Date].[Year] ) )
VAR _count1 =
    CALCULATE ( DISTINCTCOUNT ( 'DateDim'[Date].[Year] ), ALL ( 'DateDim' ) )
RETURN
    IF (
        _count = _count1,
        CALCULATE ( DISTINCTCOUNT ( 'Members'[pkey] ) ),
        CALCULATE (
            [Total Count distinct pkey],
            FILTER ( ALLSELECTED ( 'DateDim' ), 'DateDim'[Date] = MAX ( 'DateDim'[Date] ) )
        )
    )

     

    Best Regards

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi mayurwadhwani ,

You can create a measure as below:

Total Count distinct pkey = 
VAR _selyear =
    SELECTEDVALUE ( 'DateDim'[Date].[Year] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Members'[pkey] ),
        FILTER (
            'Members',
            YEAR ( 'Members'[StartDate] ) <= _selyear
                && YEAR ( 'Members'[EndDate] ) >= _selyear
        )
    )​

Best Regards

 

 

 

  • mayurwadhwani's avatar
    mayurwadhwani
    Helper I
    5 years ago

    Thanks Anonymous  for the response. But if we keep card visual and show total members irrespective of year. And if we select multiple year from slicer, it shows incorrect value. As SELECTEDVALUE function results blank with multiple selection of year slicer. 
    Tried to solve this using VALUES function but no luck.

    Can we achieve this using DAX?

     

    Thanks

      • mayurwadhwani's avatar
        mayurwadhwani
        Helper I
        5 years ago

        Hi Anonymous ,

         

        If we select all the year in slicer it should show as 8 distinct key. In pbix, its coming out to be 4. Its due to SELECTEDVALUE function. As multiple selection is returning blank.

        Thanks