Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ppgandhi11
Helper V
Helper V

DAX formula help

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.

1 ACCEPTED 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

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.