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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
anee123
Helper I
Helper I

Some filters don't work in DAX but works fine in visual level filter, we need this filter in DAX

Hi,

 

I have a boolean filed as shown below. This is a calculated column

 Sample1_IsSamePeriodLY = IF('Sample1_Calendar date'[Date] >= DATE(YEAR(TODAY()), 1, 1), 1 &&
IF('Sample1_Calendar date'[Date] <= TODAY()-1, 1, 0))

 

I want this filter to be part of the my previous ytd calculation, however when i use this in visual or page level filters, it works fine and gives only true records.

 

if i use it inside dax like, it does not filter the true values. The main reason behind putting this in dax is i need to calculate Variance %, if i put visual level filters for %variance, the results are wrong, I need the filter in dax so that 

the % calculation is accurate. Can you please look into it. PYTD, YTD and Variance all are measures.

 

PYTD Sample1 =
CALCULATE (
DISTINCTCOUNT(Sample1[activation_id]),SAMEPERIODLASTYEAR('Sample1_Calendar date'[Date]),
KEEPFILTERS(Sample1[paid_unpaid] ="Paid"),
KEEPFILTERS(Sample1[RealRegion_Sample1] <> "OTHER"),
KEEPFILTERS(Sample1[RealRegion_Sample1] <> BLANK())
('Sample1_Calendar date'[Sample1_IsSamePeriodLY] = TRUE())
)

 

 

YTD Sample1 =
CALCULATE (
DISTINCTCOUNT(Sample1[activation_id]),
KEEPFILTERS('Saample1_Calendar date'[Date]>= date(2021,01,01)),
keepfilters('Sample1_Calendar date'[Date] <= date(2021,12,31)),
KEEPFILTERS(Sample1[paid_unpaid] = "Paid"),
KEEPFILTERS(Sample1RealRegion] <> "ABC"),
KEEPFILTERS(Sample1[RealRegion]<> "OTHER"),
KEEPFILTERS(Sample1[RealRegion]<> BLANK()))

 

%Variance_Sample1 =
VAR __BASELINE_VALUE = [PYTD Sample1]
VAR __VALUE_TO_COMPARE = [YTD Sample1]
RETURN
DIVIDE(__VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE)

3 REPLIES 3
lbendlin
Super User
Super User

KEEPFILTERS() does not work the same way a visual, page or report filter works.  Using KEEPFILTERS in DAX - SQLBI

 

Show the expected outcome.

Hi,

 

Please find the pbix attached and the desired output is shown, I have used visual level filters and I am geeting desired % variance, but I want that the calc column(Sample1_IsSamePeriodLY)  inside DAX? is there a way?

 

Sample1_IsSamePeriodLY = IF('Sample1_Calendar'[Date]>= DATE(YEAR(TODAY()), 1, 1), 1 &&
                                     IF('Sample1_Calendar'[Date] <= TODAY()-1, 1, 0))

 

https://drive.google.com/file/d/1q2NjptMzZlFOU1DtDIRGZ0fHjQl97-X1/view?usp=sharing

@lbendlin @amitchandak  did you get a chance to look at my above reply.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors