Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi all,
I am new to Power Bi and I have a data with a so many columns and with a target column ( values are 0 or 1 ).
I want to select the common values between the rows that have 0 as target and the rows that have 1 as target without using any measure.
For example :
if we have this input :
Column A | Column B | Column C | Column D | Column E | Target |
50 | 60 | 40 | 30 | 22 | 0 |
60 | 33 | 20 | 30 | 22 | 0 |
50 | 33 | 100 | 33 | 22 | 1 |
22 | 445 | 333 | 333 | 00 | 1 |
600 | 222 | 20 | 333 | 00 | 1 |
Thou output should return all the columns that have a common values between the Target 0 and target 1
Column A | Column B | Column C | Column D | Column E | Target |
50 | 33 | - | - | 22 | - |
- | - | 20 | - | - | - |
I just want to highlight the common values that exists in my data between my targets without using any measure. If there is no common value i want to show that also.
Can anyone help me with this ?
Thank you.
Hi @MFelix ,
Thank you so much for your very clear code and example , but it seems that i am facing other issue when i run the step of grouped rows :
even that field is setted to the data type : whole number.
Do you have any idea how we can go around this ? or maybe just exclude this feature from our query ?
Thank you.
Hi @Anonymous,
This has to do with the limit of decimal values that then is converted to scientific. Try to make it has text
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi all,
Does anyone have an idea ?
Thank you.
Hi @MFelix ,
Thank you for your reply, Since that didn't work eaither so i dropped the feature for now ( it is not that important).
after running your script i am only getting one row in the new table which is not the case, i want to return all the commun value not for all the columns only but between any column.
In your opinion Is it just easier to work this on python script than just import the needed data into power bi ? ( talking from time consuming perspective ).
Or maybe we just create each time a DAX table that compares the values from only one column between the targets ?
Thank you .
Hi @Anonymous ,
You want to return the line values? for each one? not getting what you mean by
@Anonymous wrote:
..., i want to return all the commun value not for all the columns only but between any column.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
Thank you so much for your reply ,
I want to highlight the ratio of common values that both targets are sharing in each feature:
i hope this would be a better example :
Column1 | Column2 | Column3 | Target |
A | B | C | 0 |
D | B | E | 0 |
A | S | W | 1 |
D | B | W | 1 |
I want to have this information :
Column1 has 100% common values (There is only A and D in target 0 and target 1)
Column2 has 50%common values( only 1 value exists in target 1 and target0 which is B)
Column3 has 0% common values
...
PS: getting the ratio or the number of common rows is the same expected output.
So what you think ? Could we create a dax table for specefic column each time ? or we can query to resolve this ?
Thank you.
Hi @Anonymous ,
Sorry for the delay had some work issues, again for this you need to have a different approach because you want to count the values but based also on the unique values on each of the targets.
Do you want to return on the table the ratio value then? is that it?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous ,
Sorry for the question but why do you refer that you don't want any measures? Just to understand the requirement and get a more specific answer.
Based on the values you present and without using any measures maybe you can do it using a new table using power query or a DAX calculated table. Only using visualizations is difficult because you have more than one requirement you want the duplicates but that are on both of the target if they are duplicates in a single target you won't get it.
For this you need to manipulate your data being trough measures or trought a new table otherwise just a plain comparision is difficult.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix ,
Thank you for your replay, yes i don't want to use any measures, but I can use power query since i will be able to regenerate them each time i have a new input data.
Can you help me create this query ? or DAX calculated table ?
Thank you very much
Hi @Anonymous ,
Thinking outside the box you can do the following:
= Table.Group(#"Unpivoted Columns", {"Value", "Attribute"}, {{"Count", each Table.RowCount(List.Distinct(_[Target])) , type number}})
Trick is the part of the List.Distinct
Full code below and in attach PBIX.
let
Source = Table,
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Target"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Value", "Attribute"}, {{"Count", each Table.RowCount(List.Distinct(_[Target])) , type number}}),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Count] <> 1)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Count"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Value", List.Max),
Custom1 = Source,
#"Added Custom" = Table.AddColumn(Custom1, "Custom", each "DELETE"),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Custom] <> "DELETE")),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Custom"}),
#"Appended Query" = Table.Combine({#"Removed Columns1", #"Pivoted Column"})
in
#"Appended Query"
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
86 | |
84 | |
69 | |
67 | |
55 |
User | Count |
---|---|
125 | |
100 | |
90 | |
84 | |
66 |