Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have the following DAX
Var VrReqCount =
CALCULATE (
SUMX (
VALUES ( 'Dim Proj'[Proj No] ),
1
),
'Fact Table')
RETURN IF( VrReqCount >=1 , VrReqCount,0)
VALUE creates me a distinct list from the Dim Table, And if the measure value is null I add a 0.
But I have the following
Project No
1
2
3
4
5
Not Known
I basically want to add a Filter of <> "Not Known" into the VALUES step but I just cannot figure out how to do this.
Any help would be appreciated
Solved! Go to Solution.
if you just want to count distinct dimension values, you would probably want something like below:
(Adjust for the real column names for the relationship between the two tables. also, you do not need the if)
CALCULATE(
DISTINCTCOUNT('Dim Proj'[Proj No]),
'Dim Proj'[Proj No] <> 'Not Known',
CROSSFILTER('Dim Proj'[Proj Id], 'Fact Table'[Proj Id], Both)
) + 0
if you just want to count distinct dimension values, you would probably want something like below:
(Adjust for the real column names for the relationship between the two tables. also, you do not need the if)
CALCULATE(
DISTINCTCOUNT('Dim Proj'[Proj No]),
'Dim Proj'[Proj No] <> 'Not Known',
CROSSFILTER('Dim Proj'[Proj Id], 'Fact Table'[Proj Id], Both)
) + 0
short answer is below, but...
Var VrReqCount =
CALCULATE (
SUMX (
VALUES ( 'Dim Proj'[Proj No] ),
1
),
'Dim Proj'[Proj No] <> "Not Known",
'Fact Table')
RETURN IF( VrReqCount >=1 , VrReqCount,0)
User | Count |
---|---|
95 | |
86 | |
78 | |
72 | |
67 |
User | Count |
---|---|
110 | |
104 | |
84 | |
65 | |
63 |