Forum Discussion
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.
- Sorry, below correct formula
Measure = IF( CALCULATE ([Distinct Users], ALL( Testtable[DNSName] ) ) > 20 , SUM ( Testtable[EventCount] ) )
7 Replies
Hello, DataGeek16
Converted formula in DAX belowMeasure =
SUMX (
ADDCOLUMNS (
VALUES ( Testtable[DNSName] ),
"A", CALCULATE ( SUM ( Testtable[EventCount] ) )
),
[A]
)
But this formula have same behaviorMeasure = SUM ( Testtable[EventCount] )
Please, explain yor issue in more detailThanks for the quick response.
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.
Try this approach
Measure = IF( CALCULATE ([Distinct Users], ALL( Testtable[DNSName] ) > 20 ), SUM ( Testtable[EventCount] ) )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."
