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.
Hi there,
I have two tables in my SQL Server 2017 The CurrentMemberYr produce the expected results but
The CurrentMemberBr with the slicer did not as you can see in the attachment
How do I fix this? Do I need to put middle table like many to many?
Here is the code in TSQL for PowerBI (two tables)
create table CurrentMemberYr
(
Years int null,
CurrentMembers int null
)
GO
Insert into CurrentMemberYr(Years,CurrentMembers)
Values(2015,200),(2016,300),(2017,550),(2018,400)
--CurrentMemberYr in PowerBI
With myCTE
AS
(
Select Years,
CurrentMembers
from CurrentMemberYr
)
Select Years,
CurrentMembers,
Delta = CurrentMembers - Lag(CurrentMembers) Over (order by Years)
From myCTE
create table CurrentMemberBr
(
Years int null,
Branch Varchar(10) null,
CurrentMembers int null
)
Insert into CurrentMemberBr(Years,Branch,CurrentMembers) Values
(2015,'A',200),(2016,'A',300),(2017,'A',550),(2018,'A',400),
(2015,'B',200),(2016,'B',300),(2017,'B',550),(2018,'B',400),Current
(2015,'C',200),(2016,'C',300),(2017,'C',550),(2018,'C',400),
(2015,'D',200),(2016,'D',300),(2017,'D',550),(2018,'D',400)
--CurrentMemberBr in PowerBI
;With myCTE
AS
(
Select Years,
Branch,
CurrentMembers
from CurrentMemberBr
)
Select Years,
Branch,
CurrentMembers,
Delta = CurrentMembers - Lag(CurrentMembers) Over (order by Years)
From myCTE
Solved! Go to Solution.
Hello @Anonymous
Can you please try the following query and let me know if it works?
SELECT
Branch,
Years,
CurrentMembers, CASE
WHEN Branch != LAG(Branch, 1) OVER (ORDER BY Branch, Years) THEN NULL ELSE CurrentMembers - LAG(CurrentMembers,1) OVER (ORDER BY Branch, Years)
END AS Delta FROM CurrentMemberBr ORDER BY Branch, Years
Hello @Anonymous
Can you please try the following query and let me know if it works?
SELECT
Branch,
Years,
CurrentMembers, CASE
WHEN Branch != LAG(Branch, 1) OVER (ORDER BY Branch, Years) THEN NULL ELSE CurrentMembers - LAG(CurrentMembers,1) OVER (ORDER BY Branch, Years)
END AS Delta FROM CurrentMemberBr ORDER BY Branch, Years
Daniel,
Yes it works
Thank you so much
Oded Dror
Hi @Anonymous
Did you add a relationship between your two tables in the relationship tab ?
If not you should probably add a new one on an PK value.
Also check if it is a bidirectionnal filter between them
- Quentin
Quentin,
No, there is no connection between them. Table CurrentMemberYr is to show what the currect value looks like (expected results)
Table CurrentMemberBr show the error, I think I need to add dynamic parameter and convert the code to a stored proc
Thanks,
Ed Dror
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |