Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Filter by values with TRUE() to visualize

Hello everyone,

 

I want to give the user the ability to choose what to see on the datetime axis, currentDay, currentMonth or currentQuarter based on the date selection.

 

I created the following:

 

After that, I made a measure to connect the selections slider to filter only the values with TRUE() to show on the visual:

 

Dynamic Measure intervals = 
VAR SlicerID =  MAX('currents table'[ID]) 
RETURN
IF(
    ISFILTERED('currents table'[selects]),
    SWITCH(
        TRUE(),
        SlicerID = 1 , CALCULATE([Sum of My Value], FILTER('Data Table','Data Table'[currentDay]=TRUE())),
	 	SlicerID = 2 , CALCULATE([Sum of My Value],FILTER('Data Table','Data Table'[currentMonth]=TRUE())),
        SlicerID = 3 , CALCULATE([Sum of My Value],FILTER('Data Table','Data Table'[currentQuarter]=TRUE()))
        )
    )

 

Can somebody help me understand what i am doing wrong ?

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

    I build a sample to have a test.

    My sample table is as below, year/month/qtr columns are calculated columns.

    Year = YEAR(Sheet9[Date])
    Month = MONTH(Sheet9[Date])
    Qtr = QUARTER(Sheet9[Date])

    Date Table:

    Date = CALENDARAUTO()

    Select Table:

    We can build a date before slicer by Date table, and a select slicer by Select Table.

    Measure:

    Sum of value = SUM(Sheet9[Value])
    Measure = 
VAR _SelectStatus = 
    SELECTEDVALUE('Select'[Select])
VAR _SelectDate = 
    MAX('Date'[Date])
Return
IF(
    ISFILTERED('Select'[Select]),
    SWITCH(
        TRUE(),
        _SelectStatus="CurrentDay",IF(MAX(Sheet9[Date])=_SelectDate,1,0),
        _SelectStatus="CurrentMonth",IF(MAX(Sheet9[Year]) = YEAR(_SelectDate)&&MAX(Sheet9[Month])=MONTH(_SelectDate),1,0),
        _SelectStatus="CurrentQuarter",IF(MAX(Sheet9[Year]) = YEAR(_SelectDate)&&MAX(Sheet9[Qtr])=QUARTER(_SelectDate),1,0)
    )
)

    Build a Table visual by Sheet9[Date], Sheet9[Value] and Sum of value measure.

    Then we add Measure into the filter field in this table visual and set it to show items when value =1.

    Result is as below.

    Select "CurrentDay", Date = 2020/01/23

    Select "CurrentMonth", Date = 2020/01/23

    Select "CurrentQuarter", Date = 2020/01/23

    You can download the pbix file from this link: Filter by values with TRUE() to visualize

     

    Best Regards,

    Rico Zhou

     

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

     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

    I build a sample to have a test.

    My sample table is as below, year/month/qtr columns are calculated columns.

    Year = YEAR(Sheet9[Date])
    Month = MONTH(Sheet9[Date])
    Qtr = QUARTER(Sheet9[Date])

    Date Table:

    Date = CALENDARAUTO()

    Select Table:

    We can build a date before slicer by Date table, and a select slicer by Select Table.

    Measure:

    Sum of value = SUM(Sheet9[Value])
    Measure = 
VAR _SelectStatus = 
    SELECTEDVALUE('Select'[Select])
VAR _SelectDate = 
    MAX('Date'[Date])
Return
IF(
    ISFILTERED('Select'[Select]),
    SWITCH(
        TRUE(),
        _SelectStatus="CurrentDay",IF(MAX(Sheet9[Date])=_SelectDate,1,0),
        _SelectStatus="CurrentMonth",IF(MAX(Sheet9[Year]) = YEAR(_SelectDate)&&MAX(Sheet9[Month])=MONTH(_SelectDate),1,0),
        _SelectStatus="CurrentQuarter",IF(MAX(Sheet9[Year]) = YEAR(_SelectDate)&&MAX(Sheet9[Qtr])=QUARTER(_SelectDate),1,0)
    )
)

    Build a Table visual by Sheet9[Date], Sheet9[Value] and Sum of value measure.

    Then we add Measure into the filter field in this table visual and set it to show items when value =1.

    Result is as below.

    Select "CurrentDay", Date = 2020/01/23

    Select "CurrentMonth", Date = 2020/01/23

    Select "CurrentQuarter", Date = 2020/01/23

    You can download the pbix file from this link: Filter by values with TRUE() to visualize

     

    Best Regards,

    Rico Zhou

     

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you for taking the time to help. Now I see what I was doing wrong

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Anonymous 

    Are

      'Data Table'[currentDay],

      'Data Table'[currentMonth]

      'Data Table'[currentQuarter]

    calculated columns?

    If so you need to use measures. Calc columns are not dynamic

     

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      No they are calculated measure. currentday for example looks like this:

      currentDay = SELECTEDVALUE('Data Table'[Date])>=MAXX(ALLSELECTED('Data Table'[Date]),[Date])-1 && SELECTEDVALUE('Data Table'[Date])>=MAXX(ALLSELECTED('Data Table'[Date]),[Date])
       
      And give TRUE() only for today and FALSE() for all other days
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , what is the issue is facing ? Try this change 

    VAR SlicerID =  MAXX(allselected('currents table'),'currents table'[ID])

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you for responding, but its still dosent do the trick.