Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Hi @PallaviR ,
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
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.
Just creat a measure
Amount = SUM ( Tansactions[Sales] )
insert a matrix visual, placr Customers in the rows and the Amount measure in the values
No this will not help. Dynamically select the date from slicer and get the subset of transaction table.
@PallaviR
this is exactly what Powe Bi does. Why do you think you won't get the required result? Am I missing something?
Hi @PallaviR
ready to support you. Please explain the issue in details and provide sample data and screenshots
Hi @tamerj1
Can you please tell me how to add here date filters. It is not working.
What are StartDate and EndDate?
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
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
16 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |