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 the following data and I need to display them in a table visualization.
A | B | Counts | ||
user1 | user2 | 2 | ||
user2 | user1 | 2 | ||
user3 | user1 | 1 | ||
user1 | user3 | 1 |
I would like to display each pair (e.g. user1, user2) only once and skip (user2, user1 pair)
I would greatly appreciate any suggestion.
Solved! Go to Solution.
Hi @Anonymous ,
First create an index column in query editor;
Then create 2 columns as below:
Column = 'Table'[A]&'Table'[B]
Column 2 = 'Table'[B]&'Table'[A]
Then create a measure as below:
Measure =
var _previous=CALCULATE(MAX('Table'[Column]),FILTER(ALL('Table'),'Table'[Index]<MAX('Table'[Index])))
Return
IF(_previous=MAX('Table'[Column 2]),BLANK(),MAX('Table'[Counts]))
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Hi @Anonymous ,
First create an index column in query editor;
Then create 2 columns as below:
Column = 'Table'[A]&'Table'[B]
Column 2 = 'Table'[B]&'Table'[A]
Then create a measure as below:
Measure =
var _previous=CALCULATE(MAX('Table'[Column]),FILTER(ALL('Table'),'Table'[Index]<MAX('Table'[Index])))
Return
IF(_previous=MAX('Table'[Column 2]),BLANK(),MAX('Table'[Counts]))
And you will see:
For the related .pbix file,pls see attached.
Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
@Anonymous , This measure along with A and B will only give first combination
calculate(counrows(Table), filter(Table, Table[A] = "User1" && Table[B] = "User2"))
And this measure will not give the second combination
calculate(counrows(Table), filter(Table, not(Table[A] = "User2" && Table[B] = "User1")))
@amitchandak thank you for your quick response.
Unfortunately, based on your suggestion I have to create a measure for each pair.
I have a large dataset, full of such pairs.
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 |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |