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

October 28 & 29: Experts share their secrets on how to pass the Fabric Analytics Engineer certification exam—live. Learn more

Reply
Tezaroyal
Frequent Visitor

Dax Issue

Hi, 

Below is a measure for bookings between the created date column and the cancelled date column. I need to show the records that fall in the date range which i selected the slicer if any record  (created or cancel date) records that falls in the date range should show based on the slicer dates, as the below measure I'm getting all the created dates before the date range i selected 

Ex-6/19/2023 & 7/30/2023 are not in my range either (created date or cancel date) but the records are still showing. Can anyone please help me to eliminate these records by modifying the query?

 

Tezaroyal_0-1691514382327.png

Bookings = VAR __mindate = MIN('Calendar'[Date])                                                                                               
VAR __maxdate = MAX('Calendar'[Date])
VAR __curdate = MAX('report vwRevenueDashboardReport_BookingCard'[Createddate])
VAR __created =
SWITCH(
    TRUE(),
    __curdate < __mindate,
    0,
    ISBLANK(MAX('report vwRevenueDashboardReport_BookingCard'[Canceldate])),
    0,1
)
VAR __cancelled =
SWITCH(
    TRUE(),
    MAX('report vwRevenueDashboardReport_BookingCard'[Canceldate]) >= __mindate
        && MAX('report vwRevenueDashboardReport_BookingCard'[Canceldate]) <= __maxdate,
    -1,
    ISBLANK(MAX('report vwRevenueDashboardReport_BookingCard'[Canceldate]))
    ,1
)
VAR __result = __created + __cancelled
RETURN
__result
 
1 ACCEPTED SOLUTION

Hi @Nani ,

 

If you select "show items with no data", then you need to place the measure booking on the visual object filter.

vtangjiemsft_0-1691996270236.png


Or you can deselect "show items with no data".

 

Best Regards,

Neeko Tang

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

 

View solution in original post

5 REPLIES 5
Tezaroyal
Frequent Visitor

Hi @v-tangjie-msft  Thanks for the logic its working fine now, But when I wrote another measure for count of bookings (Bookings Count = SUMX('report vwRevenueDashboardReport_BookingCard',[Bookings logic]) its not showing accurate count😥, I would be grateful if you can help me on this, please

Tezaroyal_0-1691681473861.png

 

 

Hi @Tezaroyal ,

 

Based on your screenshot, -1-1+0+0+0+0+1+1+1+1+1+1+1+1=3, and the TOTAL showing 3 is correct. Can you provide your expected results and detailed logic?

 

Best Regards,

Neeko Tang

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

Hi Sir, the logic and count is working fine, but some records are still under the date range that are not showing on the table.After selecting the show items with no data, the records which are missing are populated on the table along with all the records which are not in the date range. I'm totally confused 

 



Nani_0-1691753463245.png

 



 

 

 

 

 

 

Hi @Nani ,

 

If you select "show items with no data", then you need to place the measure booking on the visual object filter.

vtangjiemsft_0-1691996270236.png


Or you can deselect "show items with no data".

 

Best Regards,

Neeko Tang

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

 

v-tangjie-msft
Community Support
Community Support

Hi @Tezaroyal ,

 

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can update the measure. 

Bookings = VAR __mindate = MIN('Calendar'[Date])                                                                                               
VAR __maxdate = MAX('Calendar'[Date])
VAR __curdate = MAX('report vwRevenueDashboardReport_BookingCard'[Createddate])
VAR __created =
SWITCH(
    TRUE(),
    __curdate < __mindate,
    0,
    ISBLANK(MAX('report vwRevenueDashboardReport_BookingCard'[Canceldate])),
    0,1
)
VAR __cancelled =
SWITCH(
    TRUE(),
    MAX('report vwRevenueDashboardReport_BookingCard'[Canceldate]) >= __mindate
        && MAX('report vwRevenueDashboardReport_BookingCard'[Canceldate]) <= __maxdate,
    -1,
    ISBLANK(MAX('report vwRevenueDashboardReport_BookingCard'[Canceldate]))
    ,1
)
VAR __result = __created + __cancelled
RETURN
IF(MAX('report vwRevenueDashboardReport_BookingCard'[Canceldate]) in VALUES('Calendar'[Date]) || MAX('report vwRevenueDashboardReport_BookingCard'[Createddate]) in VALUES('Calendar'[Date]) ,__result ,BLANK())

(3) Then the result is as follows.

vtangjiemsft_0-1691647448649.png

Best Regards,

Neeko Tang

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

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