Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count Based on two columns

Hello, I have a Table,   N_Name O_Name O_Status patID A O1 Completed 8652 A O1 Completed 8652 A O1 Completed 8652 A O1 Completed 8718 A O1 Completed 8718 A ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    Hi,

    This measure works

    Count = coalesce(CALCULATE(DISTINCTCOUNT(Data[patID]),Data[patID]<>blank()),0)

    Hope this helps.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    Why have you used the ALL function there?  Try this measure

    x_Applications Acted Upon = CALCULATE(DISTINCTCOUNT('Left Join'[LOANS.PAT_ID]), 'Left Join'[Application Status] == "Acted",'Left Join'[LOANS.PAT_ID]<>BLANK())

Ashish_Mathur's avatar
Ashish_Mathur
Super User
4 years ago

Hi,

This measure works

Count = coalesce(CALCULATE(DISTINCTCOUNT(Data[patID]),Data[patID]<>blank()),0)

Hope this helps.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Sir, a quick question, 

    How do i implement the same (i.e) avoiding the null values 

    if my original dax query is something like the below,

     

    x_Applications Acted Upon = CALCULATE(DISTINCTCOUNT('Left Join'[LOANS.PAT_ID]), FILTER(ALL('Left Join'[Application Status]), 'Left Join'[Application Status] == "Acted"))
    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      4 years ago

      Why have you used the ALL function there?  Try this measure

      x_Applications Acted Upon = CALCULATE(DISTINCTCOUNT('Left Join'[LOANS.PAT_ID]), 'Left Join'[Application Status] == "Acted",'Left Join'[LOANS.PAT_ID]<>BLANK())