Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi
I have this dax that works for filtering lending to drawn on a table.
Drawn Deals =
CALCULATE(
COUNTROWS(Lending), -- Or any other aggregation you'd like
USERELATIONSHIP(Lending[Drawn Date], DateHierarchy[Date]),
DateHierarchy[WeekNum] = SELECTEDVALUE(DateHierarchy[WeekNum])
)
What I need is a combined code that also looks at lead, approved and declined. These stages have a slicer to flip between them, which I need to trigger to the lookup on the relevant date ie if column stage slicer is lead then look at lead date, if slicer is approved look at approved date. The slicer is a week output but it's linked to date table to pull the week though. Lead is the active relationship to the date table while the others are inactive linked to the date table.
Thanks
Solved! Go to Solution.
Hi @EltonjohnWick ,
You need create stage column as table slicer and then try to create formula like below:
Drawn Deals1 =
VAR SelectedStage =
SELECTEDVALUE ( StageSlicer[Stage] )
RETURN
SWITCH (
TRUE (),
SelectedStage = "Lead",
CALCULATE (
COUNTROWS ( Lending ),
TREATAS ( VALUES ( DateHierarchy[Date] ), Lending[Lead Date] ),
DateHierarchy[WeekNum] = SELECTEDVALUE ( DateHierarchy[WeekNum] )
),
SelectedStage = "Approved",
CALCULATE (
COUNTROWS ( Lending ),
TREATAS ( VALUES ( DateHierarchy[Date] ), Lending[Approved Date] ),
DateHierarchy[WeekNum] = SELECTEDVALUE ( DateHierarchy[WeekNum] )
),
SelectedStage = "Declined",
CALCULATE (
COUNTROWS ( Lending ),
TREATAS ( VALUES ( DateHierarchy[Date] ), Lending[Declined Date] ),
DateHierarchy[WeekNum] = SELECTEDVALUE ( DateHierarchy[WeekNum] )
),
CALCULATE (
COUNTROWS ( Lending ),
TREATAS ( VALUES ( DateHierarchy[Date] ), Lending[Drawn Date] ),
DateHierarchy[WeekNum] = SELECTEDVALUE ( DateHierarchy[WeekNum] )
)
)
Best Regards,
Adamk Kong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much.
Finally works and saves me the visual clutter of having two different date slicer.
Much appreciated
Hi @EltonjohnWick ,
You need create stage column as table slicer and then try to create formula like below:
Drawn Deals1 =
VAR SelectedStage =
SELECTEDVALUE ( StageSlicer[Stage] )
RETURN
SWITCH (
TRUE (),
SelectedStage = "Lead",
CALCULATE (
COUNTROWS ( Lending ),
TREATAS ( VALUES ( DateHierarchy[Date] ), Lending[Lead Date] ),
DateHierarchy[WeekNum] = SELECTEDVALUE ( DateHierarchy[WeekNum] )
),
SelectedStage = "Approved",
CALCULATE (
COUNTROWS ( Lending ),
TREATAS ( VALUES ( DateHierarchy[Date] ), Lending[Approved Date] ),
DateHierarchy[WeekNum] = SELECTEDVALUE ( DateHierarchy[WeekNum] )
),
SelectedStage = "Declined",
CALCULATE (
COUNTROWS ( Lending ),
TREATAS ( VALUES ( DateHierarchy[Date] ), Lending[Declined Date] ),
DateHierarchy[WeekNum] = SELECTEDVALUE ( DateHierarchy[WeekNum] )
),
CALCULATE (
COUNTROWS ( Lending ),
TREATAS ( VALUES ( DateHierarchy[Date] ), Lending[Drawn Date] ),
DateHierarchy[WeekNum] = SELECTEDVALUE ( DateHierarchy[WeekNum] )
)
)
Best Regards,
Adamk Kong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This seems like a perfect use case for a calculation group. Create a base measure as
Num deals = COUNTROWS( Lending )
and then create a calculation group with items like
Lead Deals = SELECTEDMEASURE()
Drawn Deals =
CALCULATE (
SELECTEDMEASURE(),
-- Or any other aggregation you'd like
USERELATIONSHIP ( Lending[Drawn Date], DateHierarchy[Date] )
)
You don't need to manipulate the relationship for the Lead calculation item because that relationship is already active. And you don't need the SELECTEDVALUE as that filter is already being applied.
Hello @EltonjohnWick,
Can you please try the following approach:
Drawn Deals =
VAR SelectedStage = SELECTEDVALUE(StageSlicer[Stage])
RETURN
SWITCH(
SelectedStage,
"Lead",
CALCULATE(
COUNTROWS(Lending),
DateHierarchy[WeekNum] = SELECTEDVALUE(DateHierarchy[WeekNum])
),
"Approved",
CALCULATE(
COUNTROWS(Lending),
USERELATIONSHIP(Lending[Approved Date], DateHierarchy[Date]),
DateHierarchy[WeekNum] = SELECTEDVALUE(DateHierarchy[WeekNum])
),
"Declined",
CALCULATE(
COUNTROWS(Lending),
USERELATIONSHIP(Lending[Declined Date], DateHierarchy[Date]),
DateHierarchy[WeekNum] = SELECTEDVALUE(DateHierarchy[WeekNum])
),
"Drawn",
CALCULATE(
COUNTROWS(Lending),
USERELATIONSHIP(Lending[Drawn Date], DateHierarchy[Date]),
DateHierarchy[WeekNum] = SELECTEDVALUE(DateHierarchy[WeekNum])
),
BLANK()
)
Hi
Thanks for sending on, however this didn't work. I can see the stage options available for the relevant week but the table is blank when I add the measure
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
8 |