DAX Subquery For Stacked Bar Chart

[DataGeek16]

Need help in converting the following code to DAX to use in a stackedbarchart visual value.

Select Sum(A) FROM

(

  Select SUM(EventCount) as A, DNSName

       FROM Testtable 

       GROUP BY DNSName

) Query

Issue:

The need for this is because when I use a filter on the stacked bar chart visual, it checks for counts within each row of data instead of doing an overall sum on the entire dataset. Please provide measures in DAX to convert my SQL to address this. 

[popov]

Hello, @DataGeek16
Converted formula in DAX below

Measure =
SUMX (
    ADDCOLUMNS (
        VALUES ( Testtable[DNSName] ),
        "A", CALCULATE ( SUM ( Testtable[EventCount] ) )
    ),
    [A]
)

But this formula have same behavior

Measure = SUM ( Testtable[EventCount] )

Please, explain yor issue in more detail

[popov]

@DataGeek16

Try this approach
Measure = IF( CALCULATE ([Distinct Users], ALL( Testtable[DNSName] ) > 20 ), SUM ( Testtable[EventCount] ) )

[DataGeek16]

@popov

I get this error when I try to build the measure. 

Please note that [Distinct Users] is a calculated field/measure. 

Error:

"The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column."

[popov · Accepted solution]

Sorry, below correct formula
Measure = IF( CALCULATE ([Distinct Users], ALL( Testtable[DNSName] ) ) > 20 , SUM ( Testtable[EventCount] ) )

[DataGeek16]

That's awesome! This works well for my first scenario. I will test it with the other two scenarios and let you know if I have any further issues. 

@popov- Just to enhance my understanding, what is this measure doing differently that wasn't possible with the direct filtering? Please explain and thanks a bunch.

[popov]

Distinct Users is measure as you described

[DataGeek16]

Thanks for the quick response. 

@popov

You are right, this doesn't solve my issue. 

Essentially, I have two KPI values - One for distinct users and the Second one for the total number of page hits done by each of those users when they access the websites. I have other filters corresponding to Country, User Outlets, etc. in my dataset. 

On the PBI chart I also have a stacked bar chart that displays the website name and the page hits like the one below.

Users, Page Hits and Stacked Bar Chart

- Criteria is that when the distinct count of users go below 20 I shouldn't display anything. That's easy and I have done it using a IF clause.

However, when the count of distinct users is > 20, PBI still checks for data within each row of the stacked bar chart and if the distinct users for a particular site falls below 20, it doesn't display anything. In this scenario, I want it to check for the total sum of event counts for all the sites together instead of doing the check on one particular website. 

Please advise.