Forum Discussion

vnqtrang's avatar
vnqtrang
Icon for Helper I rankHelper I
4 years ago
Solved

Distinctcount with filter compare the values

Hi ,

I have the table below : 

 

True/FALSE

Server

Name

CODE

True

110.200.1999, 22.333.222

AA

EE000QQ0001

True110.200.1999BB

EE000QQ0002

True

110.200.1999, 10.200.3000

BE

EE000QQ0002

True

110.200.1999, 10.200.3000

ZE

EE000QQ0003

True

110.200.1999, 10.200.3000

RF

EE000QQ0004

True

110.200.1999, 10.200.3000

SD

EE000QQ0005

True

10.200.3000, 110.200.1999

ZD

EE000QQ0006

True

110.200.1999, 22.333.222

WQ

EE000AA0001

True

22.333.222, 110.200.1999, 22.333.333

QS

AA800VBB0001

 

110.200.1999, 22.333.222

XQ

AA800VBB0001

True

22.333.222, 110.200.1999

WQ

AA800VBB0004

True

110.200.1999, 22.333.222

AEZ

AA800VBB0001

False

22.333.222, 110.200.1999, 22.333.333

DF

AA800VBB0001

True

110.200.1999, 22.333.222

KU

AA800VBB0001

True

22.333.222, 110.200.1999, 22.333.333

ED

AP800VBB0005

 

 I would like to distinctcount the column Name with the filtre of only True (column True/FALSE) and the same CODE must have the same Server.

 

Calculate(distinctcount(tableA[Name], Filter(tableA, TableA[True/FALSE]="True" && ALL SAME [CODE] have SAME [Server]))

 

Thank you in advance.

Tg

Need Help
  • marveling's avatar
    marveling
    4 years ago

    Hi, 

     

    I think you can use this:

     

     

     

    DistinctCount =
CALCULATE (
    DISTINCTCOUNT ( 'TableA'[Name] ),
    REMOVEFILTERS (),
    SUMMARIZE ( TableA, 'TableA'[Server], 'TableA'[CODE] ),
    'TableA'[True/FALSE] = "True"
)

     

     

    Let me know if it works.

     

    Thanks,

    M

     

    If this post helps, please consider Accepting it as the solution to help other members find it more quickly.

     

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi  vnqtrang ,

    Here are the steps you can follow：

    1. Create calculated column.

    column =
IF(
    'Table'[True/FALSE]="True",
CALCULATE(
    DISTINCTCOUNT('Table'[Name]),
    FILTER(ALL('Table'),
    'Table'[True/FALSE]="True"&&'Table'[Server]=EARLIER('Table'[Server])&&
    'Table'[CODE]=EARLIER('Table'[CODE]))))

    2. Result:

    If you need pbix, please click here.

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

5 Replies

  • Sahil006090's avatar
    Sahil006090
    Regular Visitor
    4 years ago

    1. Go to the PowerQuery Editor create a duplicate of TableA(2) and filter the table with only True/False="True"

    2. Create a Custom Column to TableA(2) named Key (in my case)

     

     

     

    3. Create a GroupBy on Key and Name column.

     

     

    4- Close and Apply All the changes

    5- Create a DAX measure calculate(distinctcount(tableA (2)) and use this.

    If this works then please mark this post as solution.

     

    • vnqtrang's avatar
      vnqtrang
      Icon for Helper I rankHelper I
      4 years ago

      could you please advise the DAX, I tried mine but doesn't work.

  • marveling's avatar
    marveling
    Regular Visitor
    4 years ago

    Hi, 

     

    I think you can use this:

     

     

     

    DistinctCount =
CALCULATE (
    DISTINCTCOUNT ( 'TableA'[Name] ),
    REMOVEFILTERS (),
    SUMMARIZE ( TableA, 'TableA'[Server], 'TableA'[CODE] ),
    'TableA'[True/FALSE] = "True"
)

     

     

    Let me know if it works.

     

    Thanks,

    M

     

    If this post helps, please consider Accepting it as the solution to help other members find it more quickly.

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    Please show the expected result very clearly.  Do you definitely want only a measure solution or would you be OK with a combination of Power Query, calculated columns and measure solution?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  vnqtrang ,

    Here are the steps you can follow：

    1. Create calculated column.

    column =
IF(
    'Table'[True/FALSE]="True",
CALCULATE(
    DISTINCTCOUNT('Table'[Name]),
    FILTER(ALL('Table'),
    'Table'[True/FALSE]="True"&&'Table'[Server]=EARLIER('Table'[Server])&&
    'Table'[CODE]=EARLIER('Table'[CODE]))))

    2. Result:

    If you need pbix, please click here.

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly