Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Nepal101
Helper III
Helper III

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. 

1 ACCEPTED SOLUTION

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])

 

Distinct.png

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

6 REPLIES 6
PaulDBrown
Community Champion
Community Champion

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 )

 

 

Result.JPG

 

I've attached the sample PBIX file

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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 ?

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





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hello @PaulDBrown 
Sorry for the confusion. 
count of rows.JPG
instead of 5 rows is it possible to get 1 row of data.
5 rows are because the audit type consists of 1 to 4. is it possible to get just one row of employee key data based on it. (not sure if I need to create a calculated table for it) 
Thank you so much for helping me out truly appreciated it. 

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])

 

Distinct.png

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.