DAX DISTINCTCOUNT with filters

[EZimmet]

I’m trying to use DAX for the following two below :

I have multiple Data Centers, Devices (Servers, Switches, Ect) , within the Devices multiple Power Supplies.

After each number is established it will be pulled into a formula to calculate percentages 

This is a snip of part of the Data Source

Table:   "Script all connected and not connected Power Supply"

Site_Name	Parent_Device	Power_Supply	Status
ABC	P123	PS1	Connected
ABC	P123	PS2	NotConnected
ABC	P5555	PS1	Connected
ABC	P5555	PS2	Connected
XYZ	PP223344	PS1	Connected
XYZ	PP223344	PS2	Connected
XYZ	PP223344	PS3	Connected
XYZ	PP223344	PS4	Connected
XYZ	PP8889	PS1	NotConnected

#1

The end result will be to count the DISTINCT Parent_Device filtered by the Site_Name & Status 

• FILTER specific Data Center(s)
• FILTER on Status Connected / Not Connected
• DISTINCTCOUNT of Parent Device Num  

#2

The end result will be to COUNT all the Power Supplies filtered by the Site_Name & Status

• FILTER specific Data Center(s)
• FILTER on Status Connected / Not Connected
• COUNTROWS all Power Supplies in the

I started looking at something like the below but need to add filters unless you can think of a better way - TY 

-- --------------------------------------------------------------------------------------------------------

 VAR ABC CNT Site =

    SUMX(

        DISTINCT('Script all connected and not connected Power Supply'[Site_Name] = "ABC"),

    CALCULATE(

        DISTINCTCOUNT( 'Script all connected and not connected Power Supply'[Parent_Device])

    ))

-- --------------------------------------------------------------------------------------------------------

 VAR XYZ CNT Site =

    SUMX(

        DISTINCT('Script all connected and not connected Power Supply'[Site_Name] = "XYZ"),

    CALCULATE(

        DISTINCTCOUNT( 'Script all connected and not connected Power Supply'[Parent_Device])

    ))

-- --------------------------------------------------------------------------------------------------------

[maruthisp]

Hi EZimmet,

I tried to implement the solution fr the above original description. Please find the attached pbix file.

DAX DISTINCTCOUNT with filters.pbix

Please let me know if there is any missing things in the pbix file.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[MFelix]

Hi @EZimmet 

For the two measure you may simple use the metrics below:

Count Power Supply = COUNTROWS('Table')

Count of Parent Device = DISTINCTCOUNT('Table'[Parent_Device])

The using the Parent_Device, Status and site name on the visual you have the context for the calculation if you add a measure similar to this you can get:

Weight = DIVIDE([Count of Parent Device], [Count Power Supply])

You would only need to do some advance calculations if you don't want to use the columns as part of your context on the visual and want to have a specific value for each site, but if you use those columns for context simple DAX should work properly.

[danextian · Accepted solution]

Hi @EZimmet 

Hi @EZimmet 

If you want to get the distinct count of the values of in  a column just use DISTINCTCOUNT on that then filter it or create a virtual summarized table, filter it then counter the rows

CALCULATE (
    DISTINCTCOUNT ( 'Script all connected and not connected Power Supply'[Parent_Device] ),
    'Script all connected and not connected Power Supply'[Site_Name] = "ABC"
)


COUNTROWS (
    SUMMARIZE (
        FILTER (
            'Script all connected and not connected Power Supply',
            'Script all connected and not connected Power Supply'[Site_Name] = "ABC"
        ),
        'Script all connected and not connected Power Supply'[Parent_Device]
    )
)

**replace the filter with a different one.