cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
vandna Frequent Visitor
Frequent Visitor

Find values based on years selection in slicer(only values present in all the selected years)

I have created a table, values in it are only values which comes in all the years selected in slicer ex: if a participant id is present in all the selected year then only it will be displayed else not.

Now i want another table in which i can sow values on yearly basis for only which are present in the above table.

ex: i have 3 years selected in slicer, 15 participant ids are id's which are present in all the 3 years so suppose for 2015, i have 5000 sum ,2016 sum is 2000, 2017 sum is 7500.

so, how can i fetch respective values in this table.

I have tried creating table, but it is showing sum of all the values in that particular year irrespective of the filter(that an ID should be present in all the selected years).

The code for fetching id present in all the selected years is: Measure1 = VAR _EmptySlicer = CALCULATE ( ISFILTERED ( 'table'[CalcCensusYear] ), ALLSELECTED ( 'table'[CalcCensusYear] ) ) = FALSE() RETURN IF ( NOT ( _EmptySlicer ), IF ( CALCULATE ( DISTINCTCOUNT ( 'table'[CalcCensusYear] ), ALLSELECTED('table'[CalcCensusYear] ) = COUNTROWS ( ALLSELECTED ( 'table'[CalcCensusYear] )), 1 ), 1 )

2015 total sum 2000, participant ids 20 2016 total sum 3000 30 2017 total sum 5000 50

suppose slicer selection have 3 years (2015,2016,2017) ids present in all 3 years are 10 .sum is 6000 for individual year sum is 1000,1500,2000

current result: for 2015 total average : 2000(total sum for 2015)/30(count of common participant ids for 3 years)

expected result: 1000(sum of 2015 selected ids)/ 10(count of common ids)

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Find values based on years selection in slicer(only values present in all the selected years)

@vandna 

 

You may check if the following post helps.

https://community.powerbi.com/t5/Desktop/Fiiltering-table-visualisations-I-need-quot-AND-quot/m-p/60...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Community Support Team
Community Support Team

Re: Find values based on years selection in slicer(only values present in all the selected years)

@vandna 

 

You may check if the following post helps.

https://community.powerbi.com/t5/Desktop/Fiiltering-table-visualisations-I-need-quot-AND-quot/m-p/60...

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,694)