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
sanchit_1610
Helper I
Helper I

Want DAX to affect to view for filter selected value

Hi Team,

 

I have a filter (slicer) and a view for which i have disabled the edit interaction for the view. But i want a dax that uses the filter value to make the changes to the view. Consider following example:

 

I have a filter as date- Sample values (1st Sept 2019, 2nd Sept 2019, 4 Sept 2019, 6 Sept 2019, 8 Sept 2019, 11, Sept 2019)

 

Now when i select the date from the filter i want my view to display all the dates that falls in the same week number as the selected date in filter.

 

Kindly let me know if you need more inputs.

 

Thanks

1 ACCEPTED SOLUTION

For you first page you need to add a date table not connected to the other tables, and create this measure

mSales =
VAR _weeknum =
    CALCULATETABLE (
        VALUES ( Sheet1[WeekNum] );
        FILTER ( ALL ( Sheet1 ); Sheet1[Date] IN VALUES ( vDate[Date] ) )
    )
RETURN
    CALCULATE (
        SUM ( Sheet1[Sales] );
        FILTER ( Sheet1; Sheet1[WeekNum] IN _weeknum )
    )

For the second page, create 2 new tables, vSource and vDestination. These tables should contain Source and ID and Destination and ID, respectively. Create many-to-many relationships from these two new tables to Sheet2. The filter direction should be one-way, from vSource/vDestination to Sheet2. And use Destination and Source from these 2 new tables as slicer values.

pbix

View solution in original post

6 REPLIES 6
v-yuta-msft
Community Support
Community Support

@sanchit_1610 ,

 

Suppose you have another column called WeekNum. You may create a measure using dax like pattern below:

Result =
VAR _WeekNum =
    CALCULATE (
        MAX ( Table[WeekNum] ),
        FILTER ( Table, Table[Date] = SELECTEDVALUE ( Table[Date] ) )
    )
RETURN
    CONCATENATEX ( FILTER ( Table, Table[WeekNum] = _WeekNum ), Table[Date], "," )

Community Support Team _ Jimmy Tao

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

Hi @v-yuta-msft 

 

Thanks for the reply.

 

I am not getting the desired output. PFA the snippet of the view.Snippet_PBI.PNG

Now here i want all the dates with the weeknum=37.

 

Thanks

sturlaws
Resident Rockstar
Resident Rockstar

Hi @sanchit_1610,

 

if you could provide a representative sample report it would be much easier to provide solution. Upload it to onedrive/dropbox/other and post the link.

 

Cheers,
Sturla

Hi  @sturlaws 

I have two page in the pbix. 

 

1st Page- Here when i select a date from the filter, i want all the date in the time series chart (line chart) that has same weeknum as the the date selected in the filter.

For example: if the date selected in the filter is 9th Setember 2019 then the line chart should show all the dates for weeknum 37 i.e 9 to 14th Sept. 

 

2nd Page- Here i want the user to control the filters like source and destination, but the cart should be controlled through id. Now if the user selects source and destination from the filter, now if the source and destination have the same id then i want to show all the sources and destinations that has the id same as of source and destination. 

Consider the following example if above does npt make sense 🙂 . Now if the user selects source as london then i want all the source having id as 1 and their respective destination.Hi   I have two page in the pbix. 

 

Link for the files on One Drive:

 

https://1drv.ms/u/s!Aj3vzvbMGOfqglrYRpxBHD0AeXCs?e=QTQdaI

For you first page you need to add a date table not connected to the other tables, and create this measure

mSales =
VAR _weeknum =
    CALCULATETABLE (
        VALUES ( Sheet1[WeekNum] );
        FILTER ( ALL ( Sheet1 ); Sheet1[Date] IN VALUES ( vDate[Date] ) )
    )
RETURN
    CALCULATE (
        SUM ( Sheet1[Sales] );
        FILTER ( Sheet1; Sheet1[WeekNum] IN _weeknum )
    )

For the second page, create 2 new tables, vSource and vDestination. These tables should contain Source and ID and Destination and ID, respectively. Create many-to-many relationships from these two new tables to Sheet2. The filter direction should be one-way, from vSource/vDestination to Sheet2. And use Destination and Source from these 2 new tables as slicer values.

pbix

Hi  @sturlaws 

 have two page in the pbix. 1st Page- Here when i select a date from the filter, i want all the date in the time series chart (line chart) that has same weeknum as the the date selected in the filter. For example: if the date selected in the filter is 9th Setember 2019 then the line chart should show all the dates for weeknum 37 i.e 9 to 14th Sept. 2nd Page- Here i want the user to control the filters like source and destination, but the cart should be controlled through id. Now if the user selects source and destination from the filter, now if the source and destination have the same id then i want to show all the sources and destinations that has the id same as of source and destination. Consider the following example if above does npt make sense 🙂 . Now if the user selects source as london then i want all the source having id as 1 and their respective destination.Hi   I have two page in the pbix. 1st Page- Here when i select a date from the filter, i want all the date in the time series chart (line chart) that has same weeknum as the the date selected in the filter. For example: if the date selected in the filter is 9th Setember 2019 then the line chart should show all the dates for weeknum 37 i.e 9 to 14th Sept. 2nd Page- Here i want the user to control the filters like source and destination, but the cart should be controlled through id. Now if the user selects source and destination from the filter, now if the source and destination have the same id then i want to show all the sources and destinations that has the id same as of source and destination. Consider the following example if above does npt make sense 🙂 . Now if the user selects source as london then i want all the source having id as 1 and their respective destination.

Link for the files:

https://1drv.ms/u/s!Aj3vzvbMGOfqglrYRpxBHD0AeXCs?e=5fbzza

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.