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  ch...
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

     

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
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

 

 

ppgandhi11's avatar
ppgandhi11
Helper V
7 years ago

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!

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    7 years ago

    Well, you would have to put that in your SUMMARIZE or table perhaps and maybe use CONCATENATEX