Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I have a table Students with two columns in my input source data as shown below :-
Here Building and Floor are dependent columns for eg : Building A has 4 floors Floor1 through Floor4 and so on.
I will be showing this in my table matrix so in my tranformation, I need to exclude some of the rows to obtain the expected output.
Input Data :
Building | Floor |
A | Floor1 |
A | Floor2 |
A | Floor3 |
A | Floor4 |
B | Floor5 |
B | Floor7 |
B | Floor11 |
C | Floor15 |
D | Floor1 |
D | Floor3 |
E | Floor5 |
E | Floor8 |
E | Floor9 |
E | Floor10 |
Expected output :-
Building | Floor |
A | |
A | |
A | Floor3 |
A | Floor4 |
B | Floor5 |
B | |
B | Floor11 |
C | Floor15 |
D | Floor1 |
D | |
E | Floor5 |
E | Floor8 |
E | Floor9 |
E |
Finally the data after deletion or exclusion, should look like this. :-
Building | Floor |
A | Floor3 |
A | Floor4 |
B | Floor5 |
B | Floor11 |
C | Floor15 |
D | Floor1 |
E | Floor5 |
E | Floor8 |
E | Floor9 |
Could someone please suggest how we can achieve this through any DAX or any other method? How can I delete or exclude the unwanted rows where with dependent columns.
Kind regards
Sameer
Solved! Go to Solution.
Hi, @deb_power123
According to your description and sample data, I think that you can just simply use the “Filter data” function in the Power Query to prevent the rows with blank value in the [Floor] column to be imported into the Power BI, like this:
And you can get what you want, like this:
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
What is the criteria for excluding rows?
Hi, @deb_power123
According to your description and sample data, I think that you can just simply use the “Filter data” function in the Power Query to prevent the rows with blank value in the [Floor] column to be imported into the Power BI, like this:
And you can get what you want, like this:
Thank you very much!
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thankyou 🙂
@deb_power123 , what is the logic to delete or remove. because 2nd table says expected output
Hi @amitchandak
we dont want these rows so i want to remove them all so that my table matrix doesnt contain them.
It is a specific set of row which we donot want and it corresponds to the dependent column Building and Floor.
Can any DAX help in this case? Like filter dax where I want to remove all the rows say for Building A which corresponds to floor 1 and 2.I just want to remove all the rows corresponding to it.
Kind regards
Sameer
User | Count |
---|---|
103 | |
87 | |
77 | |
70 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |