Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
bdehning
Post Prodigy
Post Prodigy

Line and Stacked Column Chart and to not show years and months that are not part of Slicer

bdehning_0-1695419354693.png

What do I need to do to not have this Line and Stacked Column not show Years and Months not seleleted by Slicer.    All the 0 data at the beginning?

 

I only selected last 3 years of possible 7 years for this visual.  

 

 

3 REPLIES 3
v-yangliu-msft
Community Support
Community Support

Hi  @bdehning ,

 

Here are the steps you can follow:

1. Create calculated table.

Date=
var _table1=
CALENDAR(
    MIN('Table'[Date]),
    MAX('Table'[Date]))
var _table2=
SUMMARIZE(
    _table1,[Date],"Year-Month",YEAR([Date])&"-"&FORMAT([Date],"mmmm"),"Rank",RANKX(_table1,[Date],,ASC))
return
FILTER(
    _table2,
    [Rank]=MINX(FILTER(_table2,[Year-Month]=EARLIER([Year-Month])),[Rank]))

vyangliumsft_0-1695717657098.png

2. Select [Year-Month] – Sort by column – [Rank].

vyangliumsft_1-1695717657103.png

3. Create measure.

Flag =
var _select=SELECTCOLUMNS('Date',"slicer",'Date'[Year-Month])
var _sum=SUMX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&MONTH('Table'[Date])=MONTH(MAX('Table'[Date]))),[Value])
return
IF(
    YEAR(MAX('Table'[Date]))&"-"&FORMAT(MAX('Table'[Date]),"mmmm") in _select
    ||AND(_sum <>0,NOT(YEAR(MAX('Table'[Date]))&"-"&FORMAT(MAX('Table'[Date]),"mmmm")) in _select)
    ,1,BLANK())

 4. Place [Flag]in Filters, set is=1, apply filter.

vyangliumsft_2-1695717733831.png

5. Result:

vyangliumsft_3-1695717733838.png

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

I should have expained this better maybe.  Users will select the Year or Years of data they want to see, which are not Calendar Years and are based upon the Account they pull up.   That is why I have seperate columns for Years and Months.     I do want to restrict the amount of months to 72 to fit the visual. 

 

 

bdehning_0-1695760329964.png

bdehning_1-1695760380674.png

Column Y Axis and Line y-Axis are both Count.

 

How does this affect what you sent?

 

 

Ritaf1983
Super User
Super User

Hi @bdehning 
It is very challenging to understand the problem without seeing the data.
Please refer to the linked tutorial about the issue if it doesn't help, please attach the link to some dummy pbix.
https://www.enjoysharepoint.com/power-bi-show-items-with-no-data/

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.