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
justivan
Helper II
Helper II

Incorrect measure because of the context?

Hello,

Can someone please shed a light as to why my measure is giving me an incorrect values if I relate the date table to my fact table? If you total the columns it wont match 9720 and the correct total is actually not 9720

I'm guessing it's because of the [InDate] and the measure is being filtered by month based on the [InDate]. How do I fix my measure to correct this behavior?

Incorrect ( active relationship )

justivan_0-1625741267563.png

Correct ( inactive relationship )

justivan_1-1625741741451.pngMy measure is calculating the number of days between [InDate] and [OutDate].

 

 

Room Nights = 
VAR StayDates =
    GENERATE (
        ReservationList,
        ArrivalDate
        )
VAR FlagDates =
    FILTER (
        ADDCOLUMNS (
            StayDates,
            "Flag",
                IF ( [Date] >= [InDate] && [Date] < [OutDate] && [Status] <> "Can", 1, 0 )
        ),
        [Flag] = 1
    )
RETURN
    SUMX ( FlagDates, [Flag] )

 

 

PS : I want to attach my pbix file but I can't find an option to do so.

Edit : PBI File ( thanks @Anonymous )

5 REPLIES 5
Vera_33
Resident Rockstar
Resident Rockstar

Hi @justivan 

 

While you have the active relationship, it will filter out some rows like below InDate in Nov, but OutDate in Dec.

 

Vera_33_0-1625809237399.png

 

Your original measure is running very slowly as it does CROSSJOIN from your fact table and your date table, you can have a try with this one, it can reduce the query running time a lot

 

test =
VAR T1 =
    GENERATE (
        FILTER ( ReservationList, [Status] <> "Can" ),
        DATESBETWEEN ( ArrivalDate[Date], [InDate], [OutDate] - 1 )
    )
RETURN
    COUNTROWS ( FILTER ( T1, [Date] IN VALUES ( ArrivalDate[Date] ) ) )

Hi @Vera_33 ,

Thanks for your response.

Indeed it's very slow. I read up more on GENERATE then also added the DATESBETWEEN in my GENERATE. I didn't think of filtering my fact table in GENERATE. I've seen IN VALUES before when reading up but never thought of using it as of yet. Thank you for this. I'm learning a lot. 
Back to my original question, I was looking into relating my virtual table to my date table but couldn't find any resources that makes sense to me ( at least on my current knowledge 😂 ). So for this case, what would  you suggest? Just make it inactive the make it active through the measure when I need the relation say for example monthly revenues?

Hi @justivan,

 

Maybe you can use SUMX or AVERAGEX function to calculate monthly revenues.

You can relate date table in filter table.

 

If you still have some question, please don't hesitate to let me known.‌‌

 

Best Regards,

Link

 

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

 

Hi @justivan 

 

I don't seem to be able to link Date table with a virtual table column, either😂 If you don't want to expand your current fact table into another even bigger one, then yes, keep the relationships inactive, then use DAX to make it active when needed.

Anonymous
Not applicable

to attach a file use microsoft one drive or google drive etc. services and share a publicly accessible link here. 

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.