Distinctcount with filter compare the values

[vnqtrang]

Hi ,

I have the table below : 

True/FALSE	Server	Name	CODE
True	110.200.1999, 22.333.222	AA	EE000QQ0001
True	110.200.1999	BB	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

[marveling · Accepted solution]

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 · Accepted solution]

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

[Ashish_Mathur]

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?

[marveling · Accepted solution]

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.

[Sahil006090]

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]

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