cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

DAX Subquery For Stacked Bar Chart

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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

View solution in original post

7 REPLIES 7
Highlighted
Resolver III
Resolver III

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

Highlighted

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 ChartUsers, 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. 

Highlighted

@DataGeek16

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

Highlighted

@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."

Highlighted

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

View solution in original post

Highlighted

Distinct Users is measure as you described
Highlighted

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors