Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
bathbath
Regular Visitor

Slicer based on 445 columns

Hi,

I have a date table in Power BI which uses 445 Week Number.

I want to create my measure based on the slicer such that:

First Slicer: Year (2022), Month (4) and Week (3)
Second Slicer: Year (2022 or any other year), Any Month, Any week.

My measure should give me all the data between First Slicer and 2nd Slicer. How do I code that measure in DAX? 

WhatsApp Image 2023-12-06 at 2.57.02 PM.jpeg

Attached is the pic of example.

 

 

I tried this: Where I created copy of this date table but in the return statement, I am running into issue. I need to reference both tables with condition. How do I achive that?

 

Or other alternative is : If I try to use just one table and someone selects something from slicer 1 then how do I make sure that I can choose whatever from slicer 2 (coming from the same table)?

Lead Count Period 1 =
VAR StartDate =
CALCULATE(
MIN('DIM_Date_WO_Lead_Created'[Date_WO_Lead_Created_SK]),
ALLSELECTED('DIM_Date_WO_Lead_Created'[445_Year_Num]),
ALLSELECTED('DIM_Date_WO_Lead_Created'[445_Month_Num]),
ALLSELECTED('DIM_Date_WO_Lead_Created'[445_Week_Num])
)
VAR EndDate =
CALCULATE(
MAX('DIM_Date_WO_Lead_Created (2)'[Date_WO_Lead_Created_SK]),
ALLSELECTED('DIM_Date_WO_Lead_Created (2)'[445_Year_Num]),
ALLSELECTED('DIM_Date_WO_Lead_Created (2)'[445_Month_Num]),
ALLSELECTED('DIM_Date_WO_Lead_Created (2)'[445_Week_Num])
)
RETURN
CALCULATE(
SUM(FACT_Lowes_Sales_Leads[Lowes_Lead_Count]),
FILTER(
ALL('DIM_Date_WO_Lead_Created'),
'DIM_Date_WO_Lead_Created'[Calendar_Date] >= StartDate &&
'DIM_Date_WO_Lead_Created'[Calendar_Date] <= EndDate
)
)

 

Thanks,

RK

1 REPLY 1
amitchandak
Super User
Super User

@bathbath , for that you need two such tables and one of them need to be disconnected

 

Power BI How to use/compare two Date/Period slicers: https://youtu.be/WSeZr_-MiTg

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.