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 having an issue with a measure where I'm trying to count the number of rows and then display the count in the visualization by Year, Quarter, Month, and Week. I have 2 tables: NPSR and DateDimension. DateDimension is a date table.
My formula is: TotalCount = calculate(countrows(NPSR),filter(NPSR,([Date_Received]<=lastDate('DateDimension'[DateKey]) && [Date_Received]>firstdate(DateDimension[DateKey]))))
The Date_Received column is a date/time column and the DateKey column is also a date/time column.
In my DateDimension table, for each DateKey, I created columns to store the WeekStart date (the Sunday date the DateKey would correspond to), WeekStartYear (Year the WeekStart date corresponds to), StartQtrYear (YYYY - Q# the WeekStart date would correspond to), and StartMonthYear (Month, Year that the WeekStart date would correspond to).
I then try to create a line chart where the value is TotalCount. For my axis, I have 4 that are columns in the DateDimension table in this order: WeekStartYear, StartQtrYear, StartMonthYear, WeekStart. However the visualization is blank. Any help would be much appreciated.
Solved! Go to Solution.
OK, I imported your data as two Enter Data queries:
NPSR
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZcjBDQAQDAXQXf5ZUi2KWRr7r0FcGvWOzwyMBCUhyaxYySAxSox6g9WnfaNnOrFHjzFuVI/5xNo=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item_Num = _t, Date_Received = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item_Num", Int64.Type}, {"Date_Received", type date}}) in #"Changed Type"
DateDimension
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndO9CsIwFIbhWymdI8k5+WtuQXBwLh0c3MTNoXevFmpTFanvlJeQZ/vS922yYtVJak0brZa5q6PZNUd99OE0mma6H8zTKXQeugBdnN9W+YXtb9fzyiXoMnQddAU6cRQKhbrApTdJj2XAMmKZsMxYdlhWA5JfH+tDqsNSsFQsPZYBy4hltaGlN8mMZYdlodI7JrMVCpXC14CqfHN+cpdx5QJ08X833AE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [DateKey = _t, WeekStart = _t, WeekStartYear = _t, StartQtrYear = _t, StartMonthYear = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateKey", type date}, {"WeekStart", type date}, {"WeekStartYear", Int64.Type}, {"StartQtrYear", type text}, {"StartMonthYear", type date}}) in #"Changed Type"
I then created your exact measure:
TotalCount = calculate(countrows(NPSR),filter(NPSR,([Date_Received]<=lastDate('DateDimension'[DateKey]) && [Date_Received]>firstdate(DateDimension[DateKey]))))
I created a hierarchy:
I created a Line chart visualization:
Axis: Hierarchy from above (WeekStartYear Hierarchy)
Values: TotalCount
Seems to work like a champ although I do not agree with your Month level numbers, you have:
Can you provide some sample data, in text, that we could use to recreate the issue?
NPSR table
Item_Num | Date_Received |
1 | 6/2/2016 |
2 | 6/2/2016 |
3 | 6/2/2016 |
4 | 6/16/2016 |
5 | 6/16/2016 |
6 | 7/1/2016 |
7 | 7/1/2016 |
8 | 7/4/2016 |
9 | 7/4/2016 |
DateDimension Table
DateKey | WeekStart | WeekStartYear | StartQtrYear | StartMonthYear |
6/1/2016 | 5/29/2016 | 2016 | 2016 - Q2 | May, 2016 |
6/2/2016 | 5/29/2016 | 2016 | 2016 - Q2 | May, 2016 |
6/3/2016 | 5/29/2016 | 2016 | 2016 - Q2 | May, 2016 |
6/4/2016 | 5/29/2016 | 2016 | 2016 - Q2 | May, 2016 |
6/5/2016 | 6/5/2016 | 2016 | 2016 - Q2 | June, 2016 |
6/6/2016 | 6/5/2016 | 2016 | 2016 - Q2 | June, 2016 |
6/7/2016 | 6/5/2016 | 2016 | 2016 - Q2 | June, 2016 |
6/8/2016 | 6/5/2016 | 2016 | 2016 - Q2 | June, 2016 |
6/9/2016 | 6/5/2016 | 2016 | 2016 - Q2 | June, 2016 |
6/10/2016 | 6/5/2016 | 2016 | 2016 - Q2 | June, 2016 |
6/11/2016 | 6/5/2016 | 2016 | 2016 - Q2 | June, 2016 |
6/12/2016 | 6/12/2016 | 2016 | 2016 - Q2 | June, 2016 |
6/13/2016 | 6/12/2016 | 2016 | 2016 - Q2 | June, 2016 |
6/14/2016 | 6/12/2016 | 2016 | 2016 - Q2 | June, 2016 |
6/15/2016 | 6/12/2016 | 2016 | 2016 - Q2 | June, 2016 |
6/16/2016 | 6/12/2016 | 2016 | 2016 - Q2 | June, 2016 |
6/17/2016 | 6/12/2016 | 2016 | 2016 - Q2 | June, 2016 |
6/18/2016 | 6/12/2016 | 2016 | 2016 - Q2 | June, 2016 |
6/19/2016 | 6/19/2016 | 2016 | 2016 - Q2 | June, 2016 |
6/20/2016 | 6/19/2016 | 2016 | 2016 - Q2 | June, 2016 |
6/21/2016 | 6/19/2016 | 2016 | 2016 - Q2 | June, 2016 |
6/22/2016 | 6/19/2016 | 2016 | 2016 - Q2 | June, 2016 |
6/23/2016 | 6/19/2016 | 2016 | 2016 - Q2 | June, 2016 |
6/24/2016 | 6/19/2016 | 2016 | 2016 - Q2 | June, 2016 |
6/25/2016 | 6/19/2016 | 2016 | 2016 - Q2 | June, 2016 |
6/26/2016 | 6/26/2016 | 2016 | 2016 - Q2 | June, 2016 |
6/27/2016 | 6/26/2016 | 2016 | 2016 - Q2 | June, 2016 |
6/28/2016 | 6/26/2016 | 2016 | 2016 - Q2 | June, 2016 |
6/29/2016 | 6/26/2016 | 2016 | 2016 - Q2 | June, 2016 |
6/30/2016 | 6/26/2016 | 2016 | 2016 - Q2 | June, 2016 |
7/1/2016 | 6/26/2016 | 2016 | 2016 - Q2 | June, 2016 |
7/2/2016 | 6/26/2016 | 2016 | 2016 - Q2 | June, 2016 |
7/3/2016 | 7/3/2016 | 2016 | 2016 - Q3 | July, 2016 |
7/4/2016 | 7/3/2016 | 2016 | 2016 - Q3 | July, 2016 |
7/5/2016 | 7/3/2016 | 2016 | 2016 - Q3 | July, 2016 |
Based on the sample data above, my graph should show the following as I drill down to each level:
Year level
2016=9
Qtr level
Q2=7, Q3=2
Month level
June=7, July=2
Week level
5/29/16=3
6/12/16=2
6/26/16=2
7/3/16=2
Thanks in advance for your help!
OK, I imported your data as two Enter Data queries:
NPSR
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZcjBDQAQDAXQXf5ZUi2KWRr7r0FcGvWOzwyMBCUhyaxYySAxSox6g9WnfaNnOrFHjzFuVI/5xNo=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Item_Num = _t, Date_Received = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item_Num", Int64.Type}, {"Date_Received", type date}}) in #"Changed Type"
DateDimension
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndO9CsIwFIbhWymdI8k5+WtuQXBwLh0c3MTNoXevFmpTFanvlJeQZ/vS922yYtVJak0brZa5q6PZNUd99OE0mma6H8zTKXQeugBdnN9W+YXtb9fzyiXoMnQddAU6cRQKhbrApTdJj2XAMmKZsMxYdlhWA5JfH+tDqsNSsFQsPZYBy4hltaGlN8mMZYdlodI7JrMVCpXC14CqfHN+cpdx5QJ08X833AE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [DateKey = _t, WeekStart = _t, WeekStartYear = _t, StartQtrYear = _t, StartMonthYear = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateKey", type date}, {"WeekStart", type date}, {"WeekStartYear", Int64.Type}, {"StartQtrYear", type text}, {"StartMonthYear", type date}}) in #"Changed Type"
I then created your exact measure:
TotalCount = calculate(countrows(NPSR),filter(NPSR,([Date_Received]<=lastDate('DateDimension'[DateKey]) && [Date_Received]>firstdate(DateDimension[DateKey]))))
I created a hierarchy:
I created a Line chart visualization:
Axis: Hierarchy from above (WeekStartYear Hierarchy)
Values: TotalCount
Seems to work like a champ although I do not agree with your Month level numbers, you have:
So I found the problem. It turns out that my NPSR table was linked to a Product table. The Product table was also linked to another table which was linked to the DateDimenion table. So I'm guessing that even though the NPSR table was not linked directly to the DateDimension table that the relationship to the DateDimension table still carried through to the NPSR table by following the chain of the other linked tables.
And for whatever reason, I obviously can't count.
If I still lived in Cbus, I'd definitely look into the user group.
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |