Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Create a virtual table based on date and other slicer selection

I need to create a virtual table which will be a subset of fact table based on slicer selection and calendar date selection. Please help.
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi  Anonymous ,

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
CALENDAR(MIN('Table'[Transcation Date]),MAX('Table'[Transcation Date]))

2. Create measure.

Sales-Measure =
var _selectmin=MINX(ALLSELECTED('Table 2'),'Table 2'[Date])
var _seletmax=MaxX(ALLSELECTED('Table 2'),'Table 2'[Date])
var _selectpro=SELECTEDVALUE('Table'[Product])
var _selectbrand=SELECTEDVALUE('Table'[Brand])
return
SWITCH(
    TRUE(),
NOT( ISFILTERED('Table'[Product]))&&NOT( ISFILTERED('Table'[Brand])),CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),'Table'[Transcation Date]>=_selectmin&&'Table'[Transcation Date]<=_seletmax&&'Table'[Customer]=MAX('Table'[Customer]))),
NOT( ISFILTERED('Table'[Product])),CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),'Table'[Transcation Date]>=_selectmin&&'Table'[Transcation Date]<=_seletmax&&'Table'[Brand]=_selectbrand&&'Table'[Customer]=MAX('Table'[Customer]))),
NOT( ISFILTERED('Table'[Brand])),CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),'Table'[Transcation Date]>=_selectmin&&'Table'[Transcation Date]<=_seletmax&&'Table'[Product]=_selectpro&&'Table'[Customer]=MAX('Table'[Customer]))),
CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),'Table'[Transcation Date]>=_selectmin&&'Table'[Transcation Date]<=_seletmax&&'Table'[Product]=_selectpro&&'Table'[Brand]=_selectbrand&&'Table'[Customer]=MAX('Table'[Customer]))))

3. Result:

Scenario 1：

Scenario 2：

 

Best Regards,

Liu Yang

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