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

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.

ppgandhi11's avatar
ppgandhi11
Helper V
7 years ago

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

  • 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