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 have 3 tables (projects, tool A, tool B) which are connected with project number, and need to get :
1. total projects
2. projects using tool A
3. projects using tool B
4. projects using tool A and B
5. projects not using A nor B tool
is there an easy way how to do this ?
only way i could think of is to create new table and bring there column projects from projects table (150k rows) and then add column related for A, new column related for B and do Venn diagram ?
or vlookups ?
thank you for help
Solved! Go to Solution.
@Anonymous try following and tweak it from there :
Add two column in your main project table to define which project exists in which table
Project Exists in PS = VAR x = RELATED( PS[ProjectID] ) RETURN IF( x = BLANK(), 0, 1 ) Project Exists in PW = VAR x = RELATED(PW[ProjectID] ) RETURN IF( x = BLANK(), 0, 1 )
and then add a table using following DAX, it will put all projects together alongwith whcih category they are in
Total Projects = UNION( ADDCOLUMNS( Project, "Category", "Doesn't exists" ), ADDCOLUMNS( CALCULATETABLE( Project, FILTER( Project, Project[Project Exists in PS] = 1 && Project[Project Exists in PW] = 1 ) ), "Category", "Exists in Both" ), ADDCOLUMNS( CALCULATETABLE( Project, FILTER( Project, Project[Project Exists in PW] = 1 ) ), "Category", "Exists in PW" ), ADDCOLUMNS( CALCULATETABLE( Project, FILTER( Project, Project[Project Exists in PS] = 1 ) ), "Category", "Exists in PS" ) )
drop pie chart visual, use category from this new project table "Total Project" and use "count of project id" as value. I think this will get your what you are looking for.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Main table is Project .. 2 tools tables Projectsite and PW connected to Projects table ..
Can I have a Pie chart showing total projects using ProjectSite, Pw, Projectsite and PW and none of those 2 ?
I managed to connect them with " Style " table . but still not able to get the result i want
@Anonymous
Still not fully clear about your requirements.
Are you try to have pie chart based on
- name from PW and show total distinct projects
- name from PS and show total distinct projects
Is this correct?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
trying to get something like this
can you send some sample data?
Also if a project is in PW and PS, so it will show in
- project using project sies
- project using pw
- project using project sites and pw
it will account at 3 places, correct?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
should be 4, 4th is projects without any (ps nor pw)
samble below
Total projects 20 - projects with ps 10 - projects with pw 6
Pie needs to show - 3 PW + PS, 3 PW , 7 PS, 7 none
https://drive.google.com/file/d/11miEur-FMj7Ev5CJ07d6Jx7aB0don06O/view?usp=sharing
@Anonymous try following and tweak it from there :
Add two column in your main project table to define which project exists in which table
Project Exists in PS = VAR x = RELATED( PS[ProjectID] ) RETURN IF( x = BLANK(), 0, 1 ) Project Exists in PW = VAR x = RELATED(PW[ProjectID] ) RETURN IF( x = BLANK(), 0, 1 )
and then add a table using following DAX, it will put all projects together alongwith whcih category they are in
Total Projects = UNION( ADDCOLUMNS( Project, "Category", "Doesn't exists" ), ADDCOLUMNS( CALCULATETABLE( Project, FILTER( Project, Project[Project Exists in PS] = 1 && Project[Project Exists in PW] = 1 ) ), "Category", "Exists in Both" ), ADDCOLUMNS( CALCULATETABLE( Project, FILTER( Project, Project[Project Exists in PW] = 1 ) ), "Category", "Exists in PW" ), ADDCOLUMNS( CALCULATETABLE( Project, FILTER( Project, Project[Project Exists in PS] = 1 ) ), "Category", "Exists in PS" ) )
drop pie chart visual, use category from this new project table "Total Project" and use "count of project id" as value. I think this will get your what you are looking for.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi experts,
I have 3 tables which contained item code - name. I can connect either Sell_in[item code] or Sell_out[item code] to Item[Item code]. Not both.
Could you please help me solve it. Much appriciate
update table name and field name in dax expression basedo on your model.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
can you share your dataset?
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |