cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sowmiya Member
Member

DAX

Hi Community,
Please help me with DAX Requirement.

My raw data is,

YearMonth No.Value
201712%
201723%
201734%
201745%
201756%
201767%
201778%
201789%
2018110%
2018211%
2018312%
2018413%
2018514%
2018615%
2018716%
2018817%
2018918%
2019119%
2019220%
2019321%
2019422%
2019525%
2019626%


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

Requirement.pngEx: 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

1 REPLY 1
Pauwnrajpp Member
Member

Re: DAX

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 35 members 1,068 guests
Please welcome our newest community members: