Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi. I want to achieve this kind of visualisation. However. I don't know how to create a query that will be able to count the number of permanent, contractor, and vacancy per month. I just want to create this summary table from blank query and automatically update the query once another table is updated.
A kind of Table/Query that I want to achieve in PBI without importing an excel as data source. It summarizes the data below. The table should automatically count each contract type per month
Is this possible through DAX measure without importing a data source from excel? The Allocation should automatically contract type per month.
This is the data I have now which will be the basis for an automatic update of summary table above.
This is the visualisation I want to achieve.
Thanks in advance for your help
Solved! Go to Solution.
@third_hicana no sorry, with first one I refered to the first code i gave you about the new table.
Create the Table Type with this code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PKylKTC7JL1LSUTJWitWJVgpILcpNzEvNK4GLhCUmJ+YlV0L4sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, DUMMY = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}})
in
#"Changed Type"
then a new table with:
let
Source = Table,
#"Added Custom1" = Table.AddColumn(Source, "DUMMY", each "3"),
#"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"DUMMY"}, Type, {"DUMMY"}, "Type", JoinKind.LeftOuter),
#"Expanded Type" = Table.ExpandTableColumn(#"Merged Queries", "Type", {"Type"}, {"Type.1"}),
#"Added Custom" = Table.AddColumn(#"Expanded Type", "Count", each if [Contract Type] = [Type.1] then 1 else 0),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Start Date", "Type.1"}, {{"Count", each List.Sum([Count]), type number}})
in
#"Grouped Rows"
BBF
Hi @BeaBF So I created this bar graph. But it does not pick up the previous month count for the permanent (btw I changed it to "payroll". Is there any way that the dax can maintain the number of payroll, contractor and vacancy each month? It will just change if there is a date entered in the end date column. Thank you.
For example, if there are 10 contractors on January, it will also appear 10 contractors on February as long as their no date in the end date column.
This is I wish to achieve excluding the line graph.
@BeaBF I tried to make a visualisation. However, I can't achieve the second one. I think it is because the counts from previous month does not pick up the next month. Apreciate if you can assist me in achieving the second bar graph.
@third_hicana Could you by any chance share the PBI? in order to work in parallel on your own data
BBF
Hi @BeaBF I am not sure if I can attach here the PBi file. Do you know how can I send it here?
you can drag and drop a file below the reply
@BeaBF I have no the same feature of attaching file in reply as yours. I'm not sure if that is because I am not a premium subscriber 🤣 I'm just using pro
@third_hicana yes, it is possible. Can you paste some sample data of the table on which calculate the summary one? What does the summary table have to do?
BBF
@BeaBF Here's the sampe data.
Resource Name | Contract Type | Skill Type | Maximum Capacity | Start Date | End Date | Status |
Ana | Contractor | PM | 100% | 01/01/2022 | 04/11/2022 | |
Bart | Permanent | SPM | 100% | 01/02/2022 | Ongoing | |
Cathy | Permanent | SPM | 90% | 01/03/2022 | Ongoing | |
Dennis | Permanent | SPM | 70% | 01/04/2022 | Ongoing | |
Vacant | Vacancy | PRM | 100% | No Started | ||
Edward | Contractor | PM | 50% | 01/04/2022 | Ongoing | |
Faye | Contractor | PM | 30% | 01/03/2022 | Ongoing | |
George | Contractor | PM | 30% | 01/04/2022 | Ongoing |
This is the query I want to achieve. It should count the number of each contract type per month without importing a table from excel. I put zero for Ana because her contract was already finished.
@third_hicana here the code:
let
Source = Table,
#"Added Custom" = Table.AddColumn(Source, "Count", each if [Status] = "Ongoing" then 1 else 0),
#"Extracted Month Name" = Table.TransformColumns(#"Added Custom", {{"Start Date", each Date.MonthName(_), type text}}),
#"Grouped Rows" = Table.Group(#"Extracted Month Name", {"Start Date", "Contract Type"}, {{"Count", each List.Sum([Count]), type number}})
in
#"Grouped Rows"
Table in first line is your native table, from which create the summary one.
BBF
Hi @BeaBF This is awesome. A slight modification. Is there anyway that the count does not depend on the start date per se. So here are the rules
1. Every month (which is dependent on calendar and not on start date) should have 3 rows which are count of permanent, count of contractor, count of vacancy.
2. Vacancy should be counted repeatedly every calendar month if is still have no input in "Start Date"
3. Calendar should be automatic as time goes by. It is dependent to current month and automatically generate the 3 rows every time the calendar month starts.
This should be the table in query look like.
A bunch of thanks for your help.
@third_hicana Ok, first of all, create a new blank query as follow:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PKylKTC7JL1LSUTJWitWJVgpILcpNzEvNK4GLhCUmJ+YlV0L4sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, DUMMY = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}})
in
#"Changed Type"
then, change the code of the prevoius one in:
let
Source = Table,
#"Added Custom1" = Table.AddColumn(Source, "DUMMY", each "3"),
#"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"DUMMY"}, Type, {"DUMMY"}, "Type", JoinKind.LeftOuter),
#"Expanded Type" = Table.ExpandTableColumn(#"Merged Queries", "Type", {"Type"}, {"Type.1"}),
#"Added Custom" = Table.AddColumn(#"Expanded Type", "Count", each if [Contract Type] = [Type.1] then 1 else 0),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Start Date", "Type.1"}, {{"Count", each List.Sum([Count]), type number}})
in
#"Grouped Rows"
BBF
Hi @BeaBF I was able to do the first code.
For thre second one, I still got an error. Let me know if I am not doing it right. My table name is Resource List.
let
Source = #"Resource List",
#"Added Custom1" = Table.AddColumn(Source, "DUMMY", each "3"),
#"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"DUMMY"}, Type, {"DUMMY"}, "Type", JoinKind.LeftOuter),
#"Expanded Type" = Table.ExpandTableColumn(#"Merged Queries", "Type", {"Type"}, {"Type.1"}),
#"Added Custom" = Table.AddColumn(#"Expanded Type", "Count", each if [Contract Type] = [Type.1] then 1 else 0),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Start Date", "Type.1"}, {{"Count", each List.Sum([Count]), type number}})
in
#"Grouped Rows"
You are using Resource List as the name of the actual Table, but it is the name of the native Table. So give the new Table name "Table" and use this code
Hi @BeaBF . I am not sure if this is what you mean. Apology. So I'm getting an error 😞
The encircled query is the first code. The query above is the one with error. Is the native table is the first code? Sorry, still learning Dax
@third_hicana Let's recap.
Create a first table, named "Type", with this code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PKylKTC7JL1LSUTJWitWJVgpILcpNzEvNK4GLhCUmJ+YlV0L4sQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, DUMMY = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}})
in
#"Changed Type"
in this way:
Then, create a second table, named "Table" with this code:
let
Source = #"Resource List",
#"Added Custom1" = Table.AddColumn(Source, "DUMMY", each "3"),
#"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"DUMMY"}, Type, {"DUMMY"}, "Type", JoinKind.LeftOuter),
#"Expanded Type" = Table.ExpandTableColumn(#"Merged Queries", "Type", {"Type"}, {"Type.1"}),
#"Added Custom" = Table.AddColumn(#"Expanded Type", "Count", each if [Contract Type] = [Type.1] then 1 else 0),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Start Date", "Type.1"}, {{"Count", each List.Sum([Count]), type number}})
in
#"Grouped Rows"
in this way:
Follow the screenshots.
BBF
Hi @BeaBF Yay! Now it works. Thank you so much for your help and patience 🙂 . My last question would be does the 3 rows will automatically generate for next month and count the types? 🙂 Thanks again
@third_hicana Yes, you have to refresh the table using the appropriate button and it will automatically populate with the new month and the corresponding counts.
BBF