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.
I am trying to get a matrix to display the results of this measure. Essentially, I want it to flag a user with a 1 if the clicks in the last 3 months are greater than or equal to 3 and flag with a 0 otherwise. However, the measure shown below is not evaulating as intended.
Active =
I've uploaded the sample data here:
https://drive.google.com/file/d/1S5mw2j6yK11k18JuUA0tnWVGqniNGYCm/view?usp=sharing
I am expecting 2022-03 in the Active tab to evaluate to a total of 15,825
Sorry use this one instead
https://drive.google.com/file/d/1gnQj9E1XgFEZp1zZyS7USlPn88S6vRLv/view?usp=sharing
Hi @rjiang
You can try
VAR Active =
SUMX (
ADDCOLUMNS (
SUMMARIZE ( 'Usage Data', 'Usage Data'[gstClientID] ),
"@ClicksLast3Month", [Clicks Last 3 Months]
),
IF ( [@ClicksLast3Month] >= 3, 1, 0 )
)
Thanks. The output has changed slightly, but I'm still significantly off from what's expected
CompanyID and gstClientID
Then try this
VAR Active =
SUMX (
ADDCOLUMNS (
SUMMARIZE ( 'Usage Data', 'Usage Data'[CompanyID], 'Usage Data'[gstClientID] ),
"@ClicksLast3Month", [Clicks Last 3 Months]
),
IF ( [@ClicksLast3Month] >= 3, 1, 0 )
Closer, but still not what I'd expect.
I'm noticing that the more fields I add to the summarize function, the higher the counts go. How do I know which fields should be included or excluded?
Do you have anything else on the slicers? Maybe from other tables?
Yes, there are filters on all pages for CompanyName and ProductName
Are these two columns in the same table?
Yes they are columns in the table, but not in the matrix
Ok thebn add the to SUMMARIZE
Active =
SUMX (
ADDCOLUMNS (
SUMMARIZE (
'Usage Data',
'Usage Data'[CompanyID],
'Usage Data'[gstClientID],
'Usage Data'[CompanyName],
'Usage Data'[ProductName]
),
"@ClicksLast3Month", [Clicks Last 3 Months]
),
IF ( [@ClicksLast3Month] >= 3, 1, 0 )
)
I tried this and now it seems to be overcounting
Please send sample file. I'll to look into it
Do you have time slicer?
Tou csn also try with CALCULATE
Activew =
SUMX (
ADDCOLUMNS (
SUMMARIZE ( 'Usage Data', 'Usage Data'[CompanyID], 'Usage Data'[gstClientID] ),
"@ClicksLast3Month", [Clicks Last 3 Months]
),
CALCULATE ( IF ( [@ClicksLast3Month] >= 3, 1, 0 ) )
)
I am expecting 2022-03 in the Active tab to evaluate to a total of 15,825
https://drive.google.com/file/d/1gnQj9E1XgFEZp1zZyS7USlPn88S6vRLv/view?usp=sharing
The only thing which was missing is the Year Month column from the date table.
Active =
SUMX (
ADDCOLUMNS (
CROSSJOIN (
SUMMARIZE (
'Usage Data',
'Usage Data'[gstClientID],
'Usage Data'[gstCompanyID]
),
VALUES ( 'Date Lookup'[Year Month] )
),
"ClicksLast3Months", [Clicks Last 3 Months]
),
IF ( [Clicks Last 3 Months] >= 3, 1, 0 )
)
However I was unable to get the numbers you've mentined above
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |