Get a field data based on a slicer using dax measure

[OmarSek]

I create a table called KPI BY YEAR , with two columns YEAR and Num KPI , this table has a relationship with just Date table . 

Year           Num KPI

2020         10

2021          12

I have also a slicer on a month using a date column of table Date, i am trying just to find the NUM KPI using dax formula but it doesn't function. 

This is my dax forumula : 

NumPKIYEAR = VALUES ('KPI BY YEAR'[Num KPI]) 

[v-easonf-msft · Accepted solution]

Hi, @OmarSek 

The ‘VALUES ’ function is not normally used to return a single value. It returns a one-column table that contains the distinct values from the specified column. So it should be applied to the creation of table(such as calculated tables or  temporary tables in measure)

Please try code like below to find the NUM KPI :

NumPKIYEAR =
CALCULATE (
    MAX ( 'KPI BY YEAR'[Num KPI] ),
    FILTER (
        ALL ( 'KPI BY YEAR' ),
        'KPI BY YEAR'[Year] = SELECTEDVALUE ( 'Date'[Year] )
            && 'KPI BY YEAR'[Month] = SELECTEDVALUE ( 'Date'[Month] )
    )
)

Best Regards,
Community Support Team _ Eason

[amitchandak]

@OmarSek , a calculated table will not take slicer value.

Join with a join table, have year, month, Qtr column in the table and use that to plot year wise data in visual

[OmarSek]

@amitchandak  i understand the reason , but the can you explain more the solution. Thanks