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

[PuddleRunna]

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 🙂

[amitchandak]

@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

[PuddleRunna]

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

[v-kkf-msft]

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

[v-kkf-msft · 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.

[PuddleRunna]

Hi @v-kkf-msft ,

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