Forum Discussion

ppgandhi11's avatar
ppgandhi11
Helper V
7 years ago
Solved

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.

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    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

     

     

7 Replies

    • ppgandhi11's avatar
      ppgandhi11
      Helper V
      7 years ago

      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. 

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        7 years ago

        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.