Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
third_hicana
Helper III
Helper III

Creating a summary table without importing another source.

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

third_hicana_1-1662961434999.png

Is this possible through DAX measure without importing a data source from excel? The Allocation should automatically contract type per month.

third_hicana_0-1662964694009.png

 

 

This is the data I have now which will be the basis for an automatic update of summary table above.

third_hicana_2-1662961491357.png

 

This is the visualisation I want to achieve. 

third_hicana_0-1662961328524.png

 

Thanks in advance for your help

1 ACCEPTED SOLUTION
BeaBF
Impactful Individual
Impactful Individual

@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

View solution in original post

23 REPLIES 23
third_hicana
Helper III
Helper III

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. 

third_hicana_0-1662983029804.png

This is I wish to achieve excluding the line graph. 

 

third_hicana_1-1662983202359.png

 

 

@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. 

BeaBF
Impactful Individual
Impactful Individual

@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?

BeaBF
Impactful Individual
Impactful Individual

BeaBF_0-1662992219825.png

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

BeaBF
Impactful Individual
Impactful Individual

@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 NameContract TypeSkill TypeMaximum CapacityStart DateEnd DateStatus
AnaContractorPM100%01/01/202204/11/2022 
BartPermanentSPM100%01/02/2022 Ongoing
CathyPermanentSPM90%01/03/2022 Ongoing
DennisPermanentSPM70%01/04/2022 Ongoing
VacantVacancyPRM100%  No Started
EdwardContractorPM50%01/04/2022 Ongoing
FayeContractorPM30%01/03/2022 Ongoing
GeorgeContractorPM30%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_0-1662967872125.png

 

BeaBF
Impactful Individual
Impactful Individual

@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. 

 

third_hicana_1-1662974384366.png

 

 

A bunch of thanks for your help. 

BeaBF
Impactful Individual
Impactful Individual

@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.

third_hicana_0-1662978126860.png

 

BeaBF
Impactful Individual
Impactful Individual

@third_hicana  

 

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_1-1662979357005.png

 

 

BeaBF
Impactful Individual
Impactful Individual

@third_hicana only change in the first line:

Source = #"Resource List",

 

Try,

BBF

@BeaBF I applied the Source = #"Resource List",. However, anothe error appeared. 

third_hicana_0-1662979973186.png

third_hicana_1-1662980025489.png

 

 

BeaBF
Impactful Individual
Impactful Individual

@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:

BeaBF_0-1662980324548.png

 

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:

BeaBF_1-1662980393877.png

 

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_0-1662980820788.png

 

BeaBF
Impactful Individual
Impactful Individual

@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.

BeaBF_0-1662984340520.png

 

BBF

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors