apply slicer values in virtual table

BIuser09 — Fri, 23 Feb 2024 12:35:26 GMT

Hi,

I would like to have a measure obtaining a virtual table where all my slicers (15) selections or non-selections are applied to. With this filtered table, I still have to perform several actions to get my final measure.

Using SUMMARIZE should keep all filters, is what I've read.

There is 1 fact table (v_BI_DATA_AG), 1 calendar table (Calendar) and 2 dimension tables (v_Product_AG and vGeoAG). All relations are set.

Below does not work. Result is not filtered automatically according to all 15 slicer selections. it's not filtered at all.

How should I solve this ? Another way ?

Side question: SELECTEDVALUE only returns when 1 option in slicer is selected. How handle no selection ? And multiple selections ?

VAR SelectedMonth = SELECTEDVALUE('Calendar'[YearMonth])

VAR SelectedQuarter = SELECTEDVALUE('Calendar'[Quarter])

VAR SelectedYear = SELECTEDVALUE('Calendar'[Year])

//defining month/Q/Y

VAR curMonth = IF(NOT ISBLANK(SelectedMonth) ,

SelectedMonth,

IF(NOT ISBLANK(SelectedQuarter),

LEFT(SelectedQuarter,4) & IF(LEN(3* RIGHT(SelectedQuarter,1))<2,"0" & 3* RIGHT(SelectedQuarter,1),3* RIGHT(SelectedQuarter,1)),

SelectedYear & 12

)

VAR Curinventory = CALCULATE(SUM(v_BI_DATA_AG[Value]), v_BI_DATA_AG[TYPE]="IN", 'v_BI_DATA_AG'[Month] = CONVERT(curMonth,INTEGER) )

VAR LoopTable = SUMMARIZE( 'v_BI_DATA_AG' , 'Calendar'[YearMonth], v_BI_DATA_AG[cycle], v_Product_AG[Business], v_BI_DATA_AG[NF], vGeoAG[Region], vGeoAG[Sub Region], vGeoAG[Area],

vGeoAG[Market], v_Product_AG[Plant], v_Product_AG[Model], v_Product_AG[PL], v_Product_AG[Brand],

v_Product_AG[Family],

"SalesMonth", CALCULATE(sum(v_BI_DATA_AG[Value]), v_BI_DATA_AG[TYPE] = "SALES"),

"I", CALCULATE(sum(v_BI_DATA_AG[Value]), v_BI_DATA_AG[TYPE] = "IN")

)

Re: apply slicer values in virtual table

lbendlin — Thu, 22 Feb 2024 21:48:46 GMT

Instead of SELECTEDVALUE use VALUES. Learn about TREATAS too.

Re: apply slicer values in virtual table

BIuser09 — Fri, 23 Feb 2024 12:37:32 GMT

thanks ! using VALUES to capture slicer selection(s) solved it.

However, they have to be defined in top of measure as variables, not to use directly in SUMMARIZE function

topic Re: apply slicer values in virtual table in DAX Commands and Tips

apply slicer values in virtual table

Re: apply slicer values in virtual table

Re: apply slicer values in virtual table