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.
Background data
I have 3 tables:
Project 1.
| Project 2.
| Project 3.
|
Problem
I would like to represent this data in a chart (like below), that counts the number of IDs that have a "Yes" on any of the projects I select, the number of IDs that don't have a "Yes" on any of the projects I select but do have a "Partial", and the number of IDs that have neither "Yes" or "Partial" on any of the projects I select.
Put another way,
If Status= "Yes" for any of the rows of the selected projects with the same ID then count the ID as a "Yes", else
If Status= "Partial" for any of the rows of the selected projects with the same ID then count the ID as a "Partial", else
If Status= "No" for any of the rows of the selected projects with the same ID then count the ID as a "No".
Fail
I did Append all the Projects, and then use a Calculated Column to calculate what I need. However, I need the measure to respond to the Projects selected.
For example, if I only select Project 1 and Project 2 (on the Slicer), then the measure counts, for each Mode, the number of IDs that have a "Yes" on Project 1 or Project 2, the number of IDs that don't have a "Yes" on Project 1 or Project 2 but do have a "Partial", and the number of IDs that have neither "No" on both Project 1 or Project 2.
Thanks @v-zhangti .
When I use:
Table = UNION( SUMMARIZE('Project 1','Project 1'[ID],'Project 1'[Trains],'Project 1'[Buses],'Project 1'[Coaches],"Project","Project 1"), SUMMARIZE('Project 2','Project 2'[ID],'Project 2'[Trains],'Project 2'[Buses],'Project 2'[Coaches],"Project","Project 2"), SUMMARIZE('Project 3','Project 3'[ID],'Project 3'[Trains],'Project 3'[Buses],'Project 3'[Coaches],"Project","Project 3"))
I get:
"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."
Hi, @freemainia
You can try the following methods. Join 3 tables into one table.
Table = UNION(
SUMMARIZE('Project 1','Project 1'[ID],'Project 1'[Trains],'Project 1'[Buses],'Project 1'[Coaches],"Project","Project 1"),
SUMMARIZE('Project 2','Project 2'[ID],'Project 2'[Trains],'Project 2'[Buses],'Project 2'[Coaches],"Project","Project 2"),
SUMMARIZE('Project 3','Project 3'[ID],'Project 3'[Trains],'Project 3'[Buses],'Project 3'[Coaches],"Project","Project 3"))
Transform on top of this table. And change the column name to "Status".
Measure:
Yes = CALCULATE(COUNT('Table 2'[ID]),ALLEXCEPT('Table 2','Table 2'[Project]),'Table 2'[Status]="Yes")
No = CALCULATE(COUNT('Table 2'[ID]),ALLEXCEPT('Table 2','Table 2'[Project]),'Table 2'[Status]="No")
Partial = CALCULATE(COUNT('Table 2'[ID]),ALLEXCEPT('Table 2','Table 2'[Project]),'Table 2'[Status]="Partial")
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
When I use:
Table = UNION( SUMMARIZE('Project 1','Project 1'[ID],'Project 1'[Trains],'Project 1'[Buses],'Project 1'[Coaches],"Project","Project 1"), SUMMARIZE('Project 2','Project 2'[ID],'Project 2'[Trains],'Project 2'[Buses],'Project 2'[Coaches],"Project","Project 2"), SUMMARIZE('Project 3','Project 3'[ID],'Project 3'[Trains],'Project 3'[Buses],'Project 3'[Coaches],"Project","Project 3"))
I get:
"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."
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 |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |