topic Re: Distinct Count selected elements in a column in DAX Commands and Tips

Distinct Count selected elements in a column

OliverMatsik — Mon, 03 Aug 2020 09:11:52 GMT

Hi,

I'm struggling with a calculated column.

I'd like to make a line diagram to compare 2017 - 2019 revenue trends with 2020, with the possibility to filter between 2017 - 2019.

So if you have all years selected, the sum of the 3 years revenue is diveded by 3, if you select two years then by 2 and if you only click on one year then by 1.

I wrote a measure which seems to work perfectly but when I copy it in to the calculated column it doesn't work at all.

It basically says: "If 2020 is part of the filter than deduct one, if it is not then count the years up to 3 (there are more years but I'm only showing 2017, 2018, 2019)

IF(IF(CONTAINS(FinancialData,FinancialData[Date].[Year],2020) = TRUE ,

DISTINCTCOUNT(FinancialData[Date].[Year]) - 1, DISTINCTCOUNT(FinancialData[Date].[Year]))>=3 , 3 ,IF(CONTAINS(FinancialData,FinancialData[Date].[Year],2020) = TRUE ,

DISTINCTCOUNT(FinancialData[Date].[Year]) - 1, DISTINCTCOUNT(FinancialData[Date].[Year]))))

Can someone help me out how to make it work in a calculated column?

BR,

Oliver

Re: Distinct Count selected elements in a column

amitchandak — Mon, 03 Aug 2020 10:18:48 GMT

OliverMatsik , You can create two slicers one on depending on the date table and another on an independent date.

From the date table, you can take distinct count of year and divide.

Something like this. You have use year in place of dates

https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

Re: Distinct Count selected elements in a column

Greg_Deckler — Mon, 03 Aug 2020 11:57:13 GMT

OliverMatsik Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Distinct Count selected elements in a column

Anonymous — Tue, 04 Aug 2020 07:12:45 GMT

Hi OliverMatsik ,

See if this is what you are looking at

https://www.youtube.com/watch?v=hGdYR1KR3rc

https://www.youtube.com/watch?v=MhYEqniwung

https://www.youtube.com/watch?v=dVxg_ZO1gn0

Regards,

Harsh Nathani