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,
My current formula is:
checkvalue = calculate(countrows(ALLSELECTED(Master[PlanID])), FILTER(MASTER, Master[NPI] = MAX(Master[NPI])))
When I display, it displays below:
Service checkvalue
A 2
B 5
C 4
The above formula is counting number of row for selected planID for each of the services. So if the NPI had done Service A that was associated with 2 Plans (let say X and Y) then it displays 2.
What I want is: I want to display 5 for each of the rows above. The given NPI may have done Service B under Plans (X,Y,Z,P,Q). I want to display countrows of all the plans that NPI is associated with for each of the services. How can i get:
Service checkvalue
A 5
B 5
C 5
5 is the number of plans the NPI is associated with regardless of Service. How can I obtain this? Thanks.
If I use ALL instead of ALLSELECTED in above, it is giving me the count of all the plans in the whole universe regardless of NPI value. So if the our given NPI is associated with only X,Y,Z,P,Q then it should display only 5 even though some other NPI had association with R,S,T plans.
Solved! Go to Solution.
Try something along the lines of:
checkvalue = VAR __npi = MAX([NPI]) VAR __table = SUMMARIZE(FILTER(ALL('Table'),[NPI]=__npi),[Service],"__count",COUNT([PlanID])) VAR __highest = MAXX(__table,[__count]) RETURN __highest
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Hi Greg,
Thanks for your reply. Can you please let me know which topic have i violated? You had sent this to me in another post where I had not provided sample data and/or the work I had attempted. I had been careful since then, since I believe in keeping the communication cleaner.
I fail to understand what is missing this time around? I have provided sample data, scenario and expected output. Unfortunately this blog does not allow me to attach power bi file (which I had raised initially but could not get around it).
Thanks.
There's no violation, this isn't the Power BI police, I'm just not seeing the sample source data. Did I miss that in the original post? It looked like the data you posted was your results? Sample data is always helpful.
Sample Data:
NPI PlanID Service
1 PlanA A
1 PlanB A
1 PlanA B
1 PlanB B
1 PlanC B
1 PlanD B
1 PlanE B
1 PlanA C
1 PlanB C
1 PlanC C
1 PlanD C
2 PlanK A
2 PlanM B
2 PlanL B
Expected output:
for NPI 1 via drillthrough: I am able to get to NPI 1 page.
Service Count
A 5
B 5
C 5
Try something along the lines of:
checkvalue = VAR __npi = MAX([NPI]) VAR __table = SUMMARIZE(FILTER(ALL('Table'),[NPI]=__npi),[Service],"__count",COUNT([PlanID])) VAR __highest = MAXX(__table,[__count]) RETURN __highest
Thanks Greg.
I tweaked it a little bit to get the answer.
Corrected DAX is:
checkval =
VAR npi = MAX(Master[NPI])
VAR tbl = SUMMARIZE(FILTER(ALL(Master),Master[NPI]=npi),"count",DISTINCTCOUNT(Master[PlanID]))
VAR highest = MAXX(tbl,[count])
RETURN highest
I had to remove service filter and put DISTINCTCOUNT to get the answer.
Quick question: What would give me output:
X,Y,Z,P,Q for NPI 1 in this case? I am able to get the number 5 but if i want to see what plans were there how would i go about it? would the approach be entirely different in that case?
Thanks again!
Well, you would have to put that in your SUMMARIZE or table perhaps and maybe use CONCATENATEX
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |