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

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

Anonymous's avatar
Anonymous
Not applicable
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