Dax measure to select only selected employee badge ids out of 500 employee information list.

[RavinderSunkara]

Team,

Everyday we are doing manually to select employees required for reporting out of 500 employee list using employee badgeid. Is there a way we can write DAX measure to put only the selected 50 employeebadge ids.

[jszlm]

You can create a table with required badget id. And created a new column to identify id to show up: NeedToShow=IF(ISNA(LOOKUPVALUE(tabe1[badgetid],table1[badgetid],[badgetid])),"No","Yes").

Then filter NeedToShow="Yes".

[RavinderSunkara]

We don't have access to create table in SSAS tabular cube data. We want to select random of employee badgeids from the 500 employee badge information list.

[Christann]

Hi! try this solution from @OwenAuger:

Random Selection =
VAR SampleSize = 10
VAR TableWithRand =
    ADDCOLUMNS ( OriginalTable, "Rand", RAND () )
RETURN
    TOPN ( SampleSize, TableWithRand, [Rand] )

I tried it and it works, but you have to re-edit the table each time you want a new sample.

Hope this helps!

[OwenAuger]

I found that as long as I had at least one other table created in the Query Editor (i.e. not a DAX calculated table), the Random Selection table updated on refresh.

[Phil_Seamark]

@OwenAuger

Have you played with the SAMPLE function?

https://msdn.microsoft.com/en-us/library/dn306781(v=sql.110).aspx

"If no ordering is specified, the sample will be random, not stable, and not deterministic."

[OwenAuger]

@Phil_Seamark

Yes, I did test it out on this particular question, as it would seem an ideal function for this 🙂

It turns out, despite the documentation, Power BI Desktop requires the 3rd sort order argument.

And even if I set the 3rd argument to a constant, I got the same sample on Refresh.

[Christann]

Are you looking at a random selection of ID's, a specific set, or are they dependent on another variable? This could affect what DAX Formula you need.

[RavinderSunkara]

Yes, I am looking for random selection of employee badgeID's. Only option to create new measure for SSAS tabular cube data.

We don't have access to create table in SSAS tabular cube data. We want to select random of employee badgeids from the 500 employee badge information list.

[RavinderSunkara]

Team any solution possible for random number of employee selection usind dax measures.