DAX

[Sowmiya]

Hi Community,
Please help me with DAX Requirement.

My raw data is,

Year	Month No.	Value
2017	1	2%
2017	2	3%
2017	3	4%
2017	4	5%
2017	5	6%
2017	6	7%
2017	7	8%
2017	8	9%
2018	1	10%
2018	2	11%
2018	3	12%
2018	4	13%
2018	5	14%
2018	6	15%
2018	7	16%
2018	8	17%
2018	9	18%
2019	1	19%
2019	2	20%
2019	3	21%
2019	4	22%
2019	5	25%
2019	6	26%

Required Output:
1. Year is added as filter,
 Ex: Year Filter added as Drop Down
2. Line Visual showing 2017, 2018, 2019 and All

Ex: Line Visual (X-Axis is Month No); (Value displayed in above visual is Average of Value)
DAX Requirement:
Req 1. When 2017 is alone selected in Filter, the visual should display the value for 2017 alone. (Note: "All" field should not be displayed)
    When 2018 is alone selected in Filter, the visual should display the value for 2018 alone. (Note: "All" field should not  be displayed)
    When 2019 is alone selected in Filter, the visual should display the value for 2019alone. (Note: "All" field should not be displayed)

Req 2. When 2017 and 2018 is selected in Filter, visual should display value for 2017, 2018 and All (Note: "All" field is composed of 2017 and 2018).
When 2018 and 2019 is selected in Filter, visual should display value for 2018, 2019 and All (Note: "All" field  is composed of 2018 and 2019).
When 2019 and 2017 is selected in Filter, visual should display value for 2019, 2017 and All (Note: "All" field is composed of 2019 and 2017).

Req 3. When 2017, 2018 and 2019 are selected in Filter, visual should display value for 2017, 2018, 2019 and All (Note: "All" field is composed of 2017, 2018 and 2019).

Please assist me with DAX requiremnet. 

Thanks in Advance,
With regards,
Sowmiya

[Anonymous]

Hi @Sowmiya,

I understand your requirement and Based on the raw data that you have provided, i suggest you go for the below steps,

1. Create a dimension with the distinct values of year from the Raw data. So,

      Table 1 : Your original Raw Data

      Table 2 : Dimension table with the column year (Distinct value)

2. Relate these two tables with the year column

3. Use year column from the dimension table as your report filter 

4. Average of value will be in the vaue field in the visual.

Click on the year filter and apply the properties whether you want to single select or multi select values.

Based on your selection, the visual should display the trend of it.

Hope this helps a lot and find the solution for your problem.

Regards,

Pradeep