Forum Discussion

nesselman's avatar
nesselman
Helper I
5 years ago
Solved

Using Switch to Change ROWS on TABLE from Month to Quarter to Year

Have a little twist I'd like help with:

 

I want to use the same table and allow a slicer to choose the Period displayed in Months, Quarters, or Year.

 

Build a Table 

with a measure: 

SelectedTimeGradularity = min(x_SwitchMonQtrYear[Index])
 
Added a SWITCH measure using "MIN", but of course that only gives me the the 1st month in a quarter, 1st quarter in a year, and 1st year in the range: 
_SwitchByMonthOrQuarter = SWITCH([SelectedTimeGradularity],1,min('Calendar'[YRNUMName]),2,min('Calendar'[YR-QTR]),3,min('Calendar'[Year]))
 
Added a SWITCH measure using "DATESBETWEEN", but while this produces the correct results, in the PERIOD column it is blank and does not display the Periods (months, quarters, years): 
_SwitchByMonthOrQuarter = SWITCH([SelectedTimeGradularity],1,DATESBETWEEN('Calendar'[Date],min('Calendar'[YRNUMName]),max('Calendar'[YRNUMName])),2,min('Calendar'[YR-QTR]),3,min('Calendar'[Year]))
 
What I'm looking for from the slicer that chooses the time period gradulatity is:

 

       

 

I know I can do this with a MATRIX but wanted to avoid the busy cascading views.

 

Thoughts?

 

Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  nesselman ,

     

    If I didnt understand wrongly,create a dimension table as below:

     

    Then create a measure as below:

     

    Measure = 
IF(NOT(ISFILTERED('dim table'[Column1])),BLANK(),
SWITCH(SELECTEDVALUE('dim table'[Column1]),1,
CALCULATE(SUM('Table'[Opps]),FILTER('Table','Table'[Month day] IN FILTERS('dim table'[Period]))),
2,
CALCULATE(SUM('Table'[Opps]),FILTER('Table','Table'[Quarter] IN FILTERS('dim table'[Period]))),
3,
CALCULATE(SUM('Table'[Opps]),FILTER('Table','Table'[Year] IN FILTERS('dim table'[Period])))))

     

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

2 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    5 years ago

    Hi  nesselman ,

     

    If I didnt understand wrongly,create a dimension table as below:

     

    Then create a measure as below:

     

    Measure = 
IF(NOT(ISFILTERED('dim table'[Column1])),BLANK(),
SWITCH(SELECTEDVALUE('dim table'[Column1]),1,
CALCULATE(SUM('Table'[Opps]),FILTER('Table','Table'[Month day] IN FILTERS('dim table'[Period]))),
2,
CALCULATE(SUM('Table'[Opps]),FILTER('Table','Table'[Quarter] IN FILTERS('dim table'[Period]))),
3,
CALCULATE(SUM('Table'[Opps]),FILTER('Table','Table'[Year] IN FILTERS('dim table'[Period])))))

     

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!