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")
)
