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,
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.
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |