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,
need some inspiration here 🙂
In my data model I have a quite large Dim table that contains data about assets and is pretty much the most important table.
This table contains duplicates in the column "Asset number" due to the fact that an asset can be assigned to/used by multiple people (Column "User"):
Asset Number | User | LastSCanDate | OS |
1234 | Jane Doe | 03.06.2021 | Win10 1909 |
1234 | John Doe | 03.06.2021 | Win10 1909 |
1235 | M. Jackson | 01.06.2021 | Win10 2009 |
1236 | E. Presley | 25.05.2021 | Win10 2009 |
So far so good as I need this information depending on the type of report.
However, for the report I'm creating now, I don't want the duplicates. I want the visuals to be based on the disctinct asset count.
So I created a disctinct measure on "Asset number". Now I would also need the "OS" column to come back based on disctinct asset number. This can probably be achieved by yet another measure for "OS", but:
a) I have 11 columns I would need to apply this to and
b) with measures I'm rather limited in what I need to visualize.
Is there any slim way to achieve this without having to clone the table, remove the duplicates and having to consider which table to use based on report needs?
Hope my explanation makes sense 🙂
Solved! Go to Solution.
Hi @PuddleRunna ,
Try the following formula:
measure =
var _tab = summarize(Table, Table[Asset], Table[OS])
return
countx(_tab, Table[OS])
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@PuddleRunna , Actually in the measure you can create a Table and use, but you have do it for each measure
measure
var _tab = summarize(Table, Table[Asset], Table[OS])
return
countx(_tab, _tab[OS])
You can only use x function in this case
@amitchandak Thanks. At least that would help with the visualizations.
I'm getting a "wrong syntax for Return" message though with your suggestion?
EDIT:
Sorry, my bad. Fixed it, I overwrote the "Measure =" bit. Now I'm getting "Table _tab not found" though?
Hi @PuddleRunna ,
Does your problem have been solved? If it is solved, please mark a reply which is helpful to you.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
Hi @PuddleRunna ,
Try the following formula:
measure =
var _tab = summarize(Table, Table[Asset], Table[OS])
return
countx(_tab, Table[OS])
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |