Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Filter values x-axis dynamically

Hi! I created a monthly percent difference chart using Power BI's quick measure, month-over-month change. My problem is the value for the next month (-100%) always appear in the chart. I only want up until the current month (in this case/example, February).

 

When I change the filter (visual level) of the x-axis such that all months are selected except for March, I get my desired chart. 

 

My question now is, is there a way to dynamically choose the months (in x-axis) here? I'm not talking about filtering the data used under the chart but just the values/ categories in the axis. Note that the axis I'm using is a date hierarchy - with year and level.

 

If there's no way to dynamically filter the x-axis, can you suggest another way of making this kind of chart to achieve my desired result?

 

Thanks!

 

girinpanda

Need Help
  • Vvelarde's avatar
    Vvelarde
    8 years ago

    Anonymous

     

    I Answer in PM. The answer to avoid this is:

     

    Cost MoM% = 
IF(MAX('Table1'[Date_new])<>BLANK();
IF(
	ISFILTERED('Table1'[Date_new]);
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.");
	VAR __PREV_MONTH =
		CALCULATE(
			SUM('Table1'[Cost]);
			DATEADD('Table1'[Date_new].[Date]; -1; MONTH)
		)
	RETURN
		DIVIDE(SUM('Table1'[Cost]) - __PREV_MONTH; __PREV_MONTH)
))

     

10 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    8 years ago

    Anonymous

     

    Hi add in the quick measure in the begin:

     

    IF (HASONEVALUE(Table1[Date]);

     

    Regards

    Victor

    Lima Peru

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Vvelarde,

       

      How do I add that here? Do I remove ISFILTERED?

       

      ExtendedCost MoM% =
      IF( ISFILTERED('Table'[Date]),
      ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
      VAR __PREV_MONTH =
      CALCULATE(
      SUM(''Table'[Cost]),
      DATEADD(''Table'[Date].[Date], -1, MONTH)
      )
      RETURN
      DIVIDE(SUM(''Table'[Cost]) - __PREV_MONTH, __PREV_MONTH)
      )

      • Vvelarde's avatar
        Vvelarde
        Community Champion
        8 years ago

        Anonymous

         

        Before of ISfiltered

         

        IF(HASONEVALUE(.........);

        IF (ISFILTERED(.....