Forum Discussion

Nepal101's avatar
Nepal101
Helper III
4 years ago
Solved

Creating a Measure or new table (DAX)

Hello everyone, 
Trying to create a measure or a new table here. I already have a data set with a daily grain. 
example 

Date Employee key Audit typeService
10/28/202121DE
10/28/202122DE
10/28/202123DE
10/28/202124DE
10/28/202122DE
10/28/202122DS
10/28/202122DS
10/28/202122DS
10/28/202122DS

 

DE has 1,2,3,4 as an audit type because it has 1,3,4 it should be false even if it has 2
DS has 2,2,2,2 as an audit type it should be true. as it only has 2. 
For the given date, employee key, and the service if there is 1,2,3,4 (the sequence can be either way)then it should be false. so for the given date, employee key, and the service if it is only 2 then it is true.
I need to achieve this 

Date Employee key ServiceCompleted 
10/28/20212DEFalse 
10/28/20212DSTrue 

 

For the Four lines of data, I would only need two lines of data in here which will say true if the audit type is only 2. even if the DE has one line of 2 in there. 
I really appreciate it if you can help me with this.

Thank you once again. 

General Comment
Need Help
Show and Tell
Tips and Tricks
  • PaulDBrown's avatar
    PaulDBrown
    4 years ago

    Happy to help!

    If I'm understanding your request correctly, all you need are measures with DISTINCTCOUNT:

     

    Distinct Employee = 
DISTINCTCOUNT(MainTable[Employee key ])
    Distinct Service = 
DISTINCTCOUNT(MainTable[Service])

     

     

6 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    Try the equivalent to the following measure:

     

     

    Completed =
VAR _Table =
    CALCULATETABLE (
        VALUES ( MainTable[Audit type] ),
        ALLEXCEPT (
            MainTable,
            MainTable[Date ],
            MainTable[Employee key ],
            MainTable[Service]
        )
    )
VAR _Comp =
    CALCULATETABLE (
        VALUES ( MainTable[Audit type] ),
        FILTER ( ALL(MainTable), MainTable[Audit type] <> 2 )
    )
RETURN
    IF ( COUNTROWS ( INTERSECT ( _Table, _Comp ) ) >= 1, FALSE, TRUE )

     

     

     

    I've attached the sample PBIX file

     

    • Nepal101's avatar
      Nepal101
      Helper III
      4 years ago

      Thank you PaulDBrown for the pbix file. 
      Is there a way to count the 4 rows of data as one if we count the employee it should just give me one record if the data is true then 0 if it is false then 1. 
      Is this possible ?

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        4 years ago

        Sorry, I'm not sure I follow. Can you post a depiction of what you need?

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    Nepal101 add the following measure use it in the visual along with other columns:

     

    Measure = 
VAR __type = CALCULATE ( DISTINCTCOUNT ( 'Table'[Audit type] ), ALLEXCEPT ('Table','Table'[Employee key ],'Table'[Date ],'Table'[Service] ) ) 
RETURN
__type = 1

     

    ✨ Follow us on LinkedIn

     

    Learn about conditional formatting at Microsoft Reactor

    My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡