Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
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."
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.
Thanks 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.
- 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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |