Forum Discussion

SriDatta's avatar
SriDatta
New Member
1 year ago
Solved

Subset of data

Hi, I am looking for a help on a used case I am working on. I have the following sample data and I want this measure to give the putput as mentioned below. 

 

Cust IDYearScore
1201923.45
1202234.64
2202118.9
2202034.55
3202155.78
5202362.45
2202267
5202212.67
6202037.63
6201834.98

 

Output-if we pick cust id 1

Cust IDYearScore
1202234.64
2202118.9
2202034.55
2202267
3202155.78
5202212.67
6202037.63

 

 

What I am looking for is, if a user select a customer ID (from a slicer), we should take the selected customer ID's max year and then we want to retrive only the rows where the Year is within 3 years from a given customer's maximum year.

Hope I am clear in articlulating my requirement year. I want to display this output on a table visual. 

 

Thanks in advance,

SD

Tips and Tricks
  • MFelix's avatar
    MFelix
    1 year ago

    Hi SriDatta ,

     

    Create a table with the customers ID for the slicer, then add the following measure:

     

    Filter_Rows = 
        VAR _YEARSELECTION = MAXX(
			FILTER(
				ALL('Table'),
				'Table'[Cust ID] = SELECTEDVALUE('Customers Filter'[Cust ID])
			),
			'Table'[Year]
		)
		RETURN
			IF(
				SELECTEDVALUE('Table'[Year]) > _YEARSELECTION - 3 && SELECTEDVALUE('Table'[Year]) <= _YEARSELECTION,
				1
			)

    Now use this measure has a filter on the table and select is not blank.

     

1 Reply

  • MFelix's avatar
    MFelix
    Super User
    1 year ago

    Hi SriDatta ,

     

    Create a table with the customers ID for the slicer, then add the following measure:

     

    Filter_Rows = 
        VAR _YEARSELECTION = MAXX(
			FILTER(
				ALL('Table'),
				'Table'[Cust ID] = SELECTEDVALUE('Customers Filter'[Cust ID])
			),
			'Table'[Year]
		)
		RETURN
			IF(
				SELECTEDVALUE('Table'[Year]) > _YEARSELECTION - 3 && SELECTEDVALUE('Table'[Year]) <= _YEARSELECTION,
				1
			)

    Now use this measure has a filter on the table and select is not blank.