topic Re: Create a virtual table based on date and other slicer selection in DAX Commands and Tips

Create a virtual table based on date and other slicer selection

Anonymous — Wed, 20 Apr 2022 19:02:43 GMT

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.

Re: Create a virtual table based on date and other slicer selection

tamerj1 — Wed, 20 Apr 2022 19:33:58 GMT

Hi Anonymous

ready to support you. Please explain the issue in details and provide sample data and screenshots

Re: Create a virtual table based on date and other slicer selection

Anonymous — Wed, 20 Apr 2022 20:20:14 GMT

Thank you tamerj1 . Let me explain the scenario here. I have this transaction table here.

I have a slicer to select the date range. I have slicers to selection product, brand etc. Based on date range frpm slicer and prod + brand selection as option, I have to create a virtual table which will be a subset of the transaction table. It will take only those records that fall under selected date range from slicer additionaly if there is any slicer selection done on prod/brand.

Example - If I select only date range slicer for customers between 1-Aug-2022 to 20-Sep-2022. And no selection of prod, brand. Then my output should show aggregated sales for the customer within that date range

In this case, this should be my output

Scenario 2 - Suppose

If I select only date range slicer for customers between 1-Aug-2022 to 20-Sep-2022. And Brand 2 is selected. Then my output should show aggregated sales for the customer within that date range and only customer sales with brand 2. In this case, this should be my output. Please help.

Re: Create a virtual table based on date and other slicer selection

tamerj1 — Wed, 20 Apr 2022 20:44:47 GMT

Anonymous

Just creat a measure

Amount = SUM ( Tansactions[Sales] )

insert a matrix visual, placr Customers in the rows and the Amount measure in the values

Re: Create a virtual table based on date and other slicer selection

Anonymous — Thu, 21 Apr 2022 02:40:20 GMT

No this will not help. Dynamically select the date from slicer and get the subset of transaction table.

Re: Create a virtual table based on date and other slicer selection

tamerj1 — Thu, 21 Apr 2022 03:02:38 GMT

Anonymous
this is exactly what Powe Bi does. Why do you think you won't get the required result? Am I missing something?

Re: Create a virtual table based on date and other slicer selection

Anonymous — Thu, 21 Apr 2022 10:01:32 GMT

Hi tamerj1

Can you please tell me how to add here date filters. It is not working.

SUMMARIZECOLUMNS(Transaction[Customer],FILTER('Calendar Date','Calendar Date'[Date] >= startDate && 'Calendar Date'[Date] <= endDate),"Sales", calculate(SUM(Transaction[SALES])))

Thanks,

Pallavi

Re: Create a virtual table based on date and other slicer selection

tamerj1 — Thu, 21 Apr 2022 10:07:02 GMT

What are StartDate and EndDate?

Re: Create a virtual table based on date and other slicer selection

Anonymous — Thu, 21 Apr 2022 10:09:49 GMT

var startDate = STARTOFQUARTER('Calendar Date'[Date])

var endDate = ENDOFMONTH('Calendar Date'[Date])

Re: Create a virtual table based on date and other slicer selection

tamerj1 — Thu, 21 Apr 2022 13:49:23 GMT

Anonymous

Sorry just noticed your reply. You may try this

Table1 = var startDate = STARTOFQUARTER('Calendar Date'[Date] ) var endDate = ENDOFMONTH('Calendar Date'[Date]) RETURN FILTER ( SUMMARIZE ( Transaction, Transaction[Customer], 'Calendar Date'[Date], "Sales", SUM ( Transaction[SALES] ) ), 'Calendar Date'[Date] >= startDate && 'Calendar Date'[Date] <= endDate )

Re: Create a virtual table based on date and other slicer selection

Anonymous — Mon, 25 Apr 2022 08:51:40 GMT

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