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
mohtism
Frequent Visitor

Filter data based on Dynamic date without adding filter column.

Hey everyone,


I have a requirement where user will select a date (dont have any relation with table) and data will be filtered accordingly based on following condition selected date<=effectivefrom  and selected date>effectiveto.

 

I have created the measure and its working fine as long as effective from and effective to columns are there as shown in visual (1)
But in report requirment these two columns should not be in visual as shown in view (2) it should only contain Division Code & Area Code in Matrix Visual.

So when i remove them result changes as shown in view (2).


So is there any way to achieve it?

Effort that i did:

I did applied it using direct query but its too slow and i want it done in import mode.Also dont want other approach which is hiding the column by turning of wrap text as reports will be sent as in excel.

 

mohtism_2-1679505621814.png

 

 

 

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

Try

Sum of quantity =
VAR ChosenDate =
    SELECTEDVALUE ( 'Date'[Date] )
VAR Result =
    SUMX (
        FILTER (
            'Table',
            ChosenDate <= 'Table'[Effective from]
                && ChosenDate > 'Table'[Effective to]
        ),
        'Table'[Quantity]
    )
RETURN
    Result

View solution in original post

4 REPLIES 4
johnt75
Super User
Super User

Try

Sum of quantity =
VAR ChosenDate =
    SELECTEDVALUE ( 'Date'[Date] )
VAR Result =
    SUMX (
        FILTER (
            'Table',
            ChosenDate <= 'Table'[Effective from]
                && ChosenDate > 'Table'[Effective to]
        ),
        'Table'[Quantity]
    )
RETURN
    Result
mohtism
Frequent Visitor

Hi @mohtism ,

 

Did you try creating a relationship between your main table and this Date table?

Can you share more on if you have a data model?

 

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

hey @Pragati11  date will be provided from a manual table and it has no relation with main table.Beside this i still did this and its not working

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.