cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PuddleRunna
Helper I
Helper I

Data model approach with table that has duplicates that are sometimes needed

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 NumberUserLastSCanDateOS
1234Jane Doe03.06.2021Win10 1909
1234John Doe03.06.2021Win10 1909
1235M. Jackson01.06.2021Win10 2009
1236E. Presley25.05.2021Win10 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 🙂

1 ACCEPTED 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.

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@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.

View solution in original post

Hi @v-kkf-msft ,

Sorry, only saw your response now. Magic, works now. Thanks!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!