Dynamic value computation

[nakul0490]

Hello,

I am working on an assignment, and need help for a problem, which is like : 

A user can come multiple times in a day and on different days and can use mobile or desktop for logging. so for the dates selected in the slicer, i have to compute each user only once and for all his  logging entries if he has used only mobile for logging i should mark him under mobile category, if he has used only desktop for logging i should mark him under desktop category, but if he has used both i should mark him under both category.

For better understanding the data is like : 

Date | User | Device

23/03/2018 | AAA | Mobile

24/04/2018 | AAA | Desktop

24/04/2018 | BBB | Desktop

25/04/2018 | BBB | Mobile

Desired output : if filter is on dates  from 24/04/2018 to 25/04/2018

AAA | Desktop

BBB | Both

I am trying creating measure but not able to achieve it,

Any guidance would be of great help.

Thanks in advance!

[Anonymous]

Yes this can be done.  First thing we need to do is create a "Key" field.  Best place to do this is part of the import, but we could also do this as a calculated column in Dax.  All you need to do is use the code "[User] & [Device]".  The code is similar for Power Query and Dax in this regard.

Once you have the key column, you can create a label measure that looks like this:

Output Text = if(
    DISTINCTCOUNT(TestData[Key]) > 1,
    "Both",
    FIRSTNONBLANK(TestData[Device], TestData[Device])
)

 

[nakul0490]

Hey,

Many Thanks for the solution, it worked.. 

is there also a way to count on the output text the number of users? i tried doing it but it just uses the "Both" category

[Anonymous]

Are you able to rephrase your question? I'm not really sure what you means.  Do you mean one of these:

Output Text = if(
    DISTINCTCOUNT(TestData[Key]) > 1,
    "Both (" & COUNT(TestData[User]) & ")",
    FIRSTNONBLANK(TestData[Device], TestData[Device])
)

Output Text = if(
    DISTINCTCOUNT(TestData[Key]) > 1,
    "Both (" & DISTINCTCOUNT(TestData[User]) & ")",
    FIRSTNONBLANK(TestData[Device], TestData[Device])
)

[nakul0490]

Hey,

Thanks a lot for looking into it again :),

I am actually trying to draw a pie chart showing number of users falling in each category  - "Both", "Desktop" & "Mobile"

like: 

Type   Count 

Both         2

Desktop   1

Mobile     1

and if I use the second code mentioned by you separately in addition to the classification of category like :

Type = if(DISTINCTCOUNT(Tabelle1[Key])>1,"Both",FIRSTNONBLANK(Tabelle1[Device],Tabelle1[Device]))

Count = if(DISTINCTCOUNT(Tabelle1[Key])>1, DISTINCTCOUNT(Tabelle1[User]),DISTINCTCOUNT(Tabelle1[User])) 

i get the result as 

User       Type          Count

A            Desktop     1

B            Both           1

but this table is not helping me in plotting the chart to indicate total users in each category.

[Anonymous]

I don't know that you can without finding some really complex trick.  You could give this a try:

https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

[nakul0490]

Thank you for your support and help, I will check the paper and will see if it can help me drawing the results 🙂

[v-juanli-msft]

Hi 

Have you get your desired results?

if not, you can raise your question or open another post for better help.

If you have solved your problem, could you kindly share your solution at your convenience?

Best regards

Maggie