Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi there,
The issue is at "distinctCount of Respondent ID". I have about 13,000 rows of data but there's only 205 unique respondent ID. In Exhibit 1, i used a simple "DISTINCTCOUNT([RespondentID])" and it returned me the "distinctCount of Respondent ID" column. Then I did a simple division measure hence i'm getting all 100% in the last column, which is wrong!
However, in Exhibit 2 is what i desire. But I manually hard-coded the "205" in it and i'm able to get what i need.
My question is : What is the right formula to get the "205"?
Exhibit 1 : This is what I'm getting
Exhibit 2 : This is what I really need
Solved! Go to Solution.
Hi,
I am not sure how your datamodel looks like, but please try something like below.
It is for creating a measure.
distinctcount expected result measure: =
CALCULATE (
DISTINCTCOUNT ( TableName[RespondentID] ),
ALL ( TableName[Value] )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi there,
The issue is at "distinctCount of Respondent ID". I have about 13,000 rows of data but there's only 205 unique respondent ID. In Exhibit 1, i used a simple "DISTINCTCOUNT([RespondentID])" and it returned me the "distinctCount of Respondent ID" column. Then I did a simple division measure hence i'm getting all 100% in the last column, which is wrong!
However, in Exhibit 2 is what i desire. But I manually hard-coded the "205" in it and i'm able to get what i need.
My question is : What is the right formula to get the "205"?
Exhibit 1 : This is what I'm getting
Exhibit 2 : This is what I really need
You can use CALCULATE(DistinctCount(Table[RespondentID), REMOVEFILTERS(Table[RespondentID]))
This will give you the 205 in each row, and then use your original measure as the numerator as in:
DIVIDE([Original Measure], [The New Measure]).
--Nate
i tried CALCULATE(DistinctCount(Table[RespondentID), REMOVEFILTERS(Table[RespondentID]))
but it still returns me the exact value as in my Exhibit 1
Just to check: are you looking for an answer in DAX? If so, you may want to post in the Power BI forum?
Hi,
I am not sure how your datamodel looks like, but please try something like below.
It is for creating a measure.
distinctcount expected result measure: =
CALCULATE (
DISTINCTCOUNT ( TableName[RespondentID] ),
ALL ( TableName[Value] )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
User | Count |
---|---|
40 | |
26 | |
22 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |