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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
srutiR2403
Frequent Visitor

Exclude a dates slicer in measure and include another date field in the meausre

Hi

I need to calculate a distinct policy based on from dt and to date from the policy table. I also have a calendar dates table which is a calculated table. User will choose the date in dates table and based on user selection I need to calculate the policycount based on the interval selected in dates table. Suppose if the user chose between 1-jan-2023 and 31-jan-2023 I need to get the policy count from policy table as from_dt<=31-jan-2023 and to_dt>=31-jan-2023

MAXPOLICYCOUNT =
VAR _max = MAX( Dates[Date] )
RETURN
CALCULATE (
DISTINCTCOUNT ( pol_table[POL_NO]) ,

pol_table[_DT_TO] >= _max , pol_table[_DT_FM] <= _max

)

Maxpolicycount1 = CALCULATE([MAXPOLICYCOUNT],USERELATIONSHIP(pol_table[_DT_TO],Dates[Date]))

srutiR2403_0-1719407840504.png

the above result is correct this is expected (maxpolicycount1). But in dashboard i would be having dates slicer so when dates slicer is present all the values are showing as incorrect so i need to avoid the dates slicer and in above output u could find i have dt_to slicer. This dt_to slicer will not be available in dashboard therfore i need to include dt_to filter in the measure and exclude dates slicer from the measure. i tried to use datesbetween for dt to field but it says it has duplicate values for the column

1 ACCEPTED SOLUTION

No this will not work as the measure created is having max(date) 

I created separate policy table unconnected with the date table and resolved this issue

View solution in original post

2 REPLIES 2
newellaa
Frequent Visitor

Not sure if I understand but it seems like you don't want the date slicer to interact with the visual that does the count. If you click on the date slicer then go to Format and Edit Interactions you can stop the slicer from interacting with other visuals.

newellaa_0-1719414788551.png

 

No this will not work as the measure created is having max(date) 

I created separate policy table unconnected with the date table and resolved this issue

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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