March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello,
I have this query where FactPXD is 1 to many to Dept and the filter direction is single from the Dept to FactPXD, how can I create this measure in DAX :
SELECT
SUM(
CASE
WHEN D.DeptName IN ('A','T') OR
(D.DeptTpe='B'
AND F.QTY < 0) THEN F.QTY
ELSE 0
END
) AS Total
FROM
FactPXD AS F
LEFT JOIN Dept AS D ON D.[Dept Id] = F.[DepId]
WHERE
D.[Dept Group] LIKE 'T%'
AND D.[Dept Group] NOT LIKE 'TC%'
Solved! Go to Solution.
Try the following :
Your measure =
CALCULATE (
SUMX (
FactPXD,
SWITCH (
TRUE(),
RELATED(Dept[DeptName]) IN {"A", "T"}, FactPXD[QTY],
RELATED(Dept[DeptTpe]) = "B" && FactPXD[QTY] < 0, FactPXD[QTY],
0
)
),
FILTER (
Dept,
LEFT(Dept[Dept Group], 1) = "T" &&
LEFT(Dept[Dept Group], 2) <> "TC"
)
)
Try the following :
Your measure =
CALCULATE (
SUMX (
FactPXD,
SWITCH (
TRUE(),
RELATED(Dept[DeptName]) IN {"A", "T"}, FactPXD[QTY],
RELATED(Dept[DeptTpe]) = "B" && FactPXD[QTY] < 0, FactPXD[QTY],
0
)
),
FILTER (
Dept,
LEFT(Dept[Dept Group], 1) = "T" &&
LEFT(Dept[Dept Group], 2) <> "TC"
)
)
@Blablabla Use SWITCH( TRUE(), ... ) for the CASE statement. Probably SUMX for summing.
@Greg_Deckler I tried the following but it doesn't recognise the columns from my dimensions :
TotalMeasure :=
CALCULATE (
SUMX (
FactPXD,
SWITCH (
TRUE(),
Dept[DeptName] IN {"A", "T"}, FactPXD[QTY],
Dept[DeptType] = "B" && FactPXD[QTY] < 0, FactPXD[QTY],
0
)
),
FILTER (
Dept,
LEFT(Dept[Dept Group], 1) = "T" &&
LEFT(Dept[Dept Group], 2) <> "TC"
)
)
These are some sample queries for verify data in SQLMS.
https://drive.google.com/file/d/1UE53tW5qDi5U0LoH5vbuLBT1MouweJHw/view?usp=sharing
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
114 | |
76 | |
57 | |
52 | |
44 |
User | Count |
---|---|
165 | |
116 | |
63 | |
57 | |
50 |