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'm struggling ot understand why this code is not filling in the blanks in this matrix. I was expecting on the row 7:00 it would add up the amounts for both dates which = 6 and put 6 in the Measure 2 value for both dates?
Measure 2 = VAR TableVar = ADDCOLUMNS ( SUMMARIZE ( ATEs, 'Time'[TimeKey],'Date'[Date] ), "ATEs", ATEs[ATE Count] ) RETURN CALCULATE( SUMX(TableVar, [ATEs]),ALL('Date'))
The ATE table looks like this. TimeKey and Date are just standard Date and Time master tables. [ATE Count] is just a countrows on ATE table.
Any clues appreciated
Mike
Solved! Go to Solution.
Hi @masplin ,
I don't know if it is the result you want. You need to create the following date table.
Date = CALENDAR(MIN('ATEs'[ATEDate]),MAX('ATEs'[ATEDate]))
Then create the following measure:
Measure 2 =
VAR TableVar =
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE ( ATEs, 'Time'[TimeKey], 'Date'[Date] ),
"ATEs", ATEs[ATE Count]
),
ALL ( 'Date' )
)
RETURN
CALCULATE ( CALCULATE ( SUMX ( TableVar, [ATEs] ), ALL ( 'Date' ) ) )
Results are as follows:
Here is a demo, please try it:
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Some times We all miss some basic thing. Please check the relation ship of all tables is it intact?
Proud to be a Super User!
yep or the first column wouldn't have worked
Hi @masplin ,
I don't know if it is the result you want. You need to create the following date table.
Date = CALENDAR(MIN('ATEs'[ATEDate]),MAX('ATEs'[ATEDate]))
Then create the following measure:
Measure 2 =
VAR TableVar =
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE ( ATEs, 'Time'[TimeKey], 'Date'[Date] ),
"ATEs", ATEs[ATE Count]
),
ALL ( 'Date' )
)
RETURN
CALCULATE ( CALCULATE ( SUMX ( TableVar, [ATEs] ), ALL ( 'Date' ) ) )
Results are as follows:
Here is a demo, please try it:
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ah is it that in my measure my TableVAR is picking up the context of the column/row of each cell i.e. a single date and time so there is nothing to add up. You VAR measure is creating a table for the whole set of dates for each time?
Hi @masplin ,
Yes, you are right, in fact, the formula can be simplified to this:
Measure 2 =
VAR TableVar =
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE ( ATEs, 'Time'[TimeKey], 'Date'[Date] ),
"ATEs", ATEs[ATE Count]
),
ALL ( 'Date' )
)
RETURN
SUMX ( TableVar, [ATEs] )
Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |