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.
Hi All,
I have a table say Student and my input source table is as shown below :-
Input Source :
Building | Floor | Attendance | TotalSeats |
A | Floor1 | 2 | 10 |
A | Floor2 | 4 | 10 |
A | Floor3 | 15 | 20 |
B | Floor2 | 1 | 5 |
B | Floor5 | 6 | 10 |
C | Floor1 | 7 | 15 |
C | Floor7 | 18 | 20 |
Rows to be removed :(Highlighted as Bold)
Building | Floor | Attendance | TotalSeats |
A | Floor1 | 2 | 10 |
A | Floor2 | 4 | 10 |
A | Floor3 | 15 | 20 |
B | Floor2 | 1 | 5 |
B | Floor5 | 6 | 10 |
C | Floor1 | 7 | 15 |
C | Floor7 | 18 | 20 |
Expected Output:
Building | Floor | Attendance | TotalSeats |
A | Floor1 | 2 | 10 |
A | Floor3 | 15 | 20 |
B | Floor5 | 6 | 10 |
C | Floor1 | 7 | 15 |
Is there any DAX query or any other method anyone can suggest to remove/delete the selected rows ?
I want to display the expected output in my Table Matrix visual.
How can I remove the unwanted rows using tranformation in PowerBI?
Please suggest.
Kind regards
Sameer
Solved! Go to Solution.
@deb_power123 , you can create a flag in power query or dax and remove those
power query
if ( [Building] ="A" and [floor] = "Floor2" ) or ( [Building] ="B" and [floor] = "Floor2" )
or ( [Building] ="C" and [floor] = "Floor18" ) then 0 else 1
filter all 0 rows in power query and save
In dax
if (( [Building] ="A" && [floor] = "Floor2" ) || ( [Building] ="B" && [floor] = "Floor2" )
|| ( [Building] ="C" && [floor] = "Floor18" ) ,0 ,1)
filter this at visual or page level, if you create a flag in DAX
@deb_power123 , you can create a flag in power query or dax and remove those
power query
if ( [Building] ="A" and [floor] = "Floor2" ) or ( [Building] ="B" and [floor] = "Floor2" )
or ( [Building] ="C" and [floor] = "Floor18" ) then 0 else 1
filter all 0 rows in power query and save
In dax
if (( [Building] ="A" && [floor] = "Floor2" ) || ( [Building] ="B" && [floor] = "Floor2" )
|| ( [Building] ="C" && [floor] = "Floor18" ) ,0 ,1)
filter this at visual or page level, if you create a flag in DAX
I used second approach and it worked as expected
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |