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
ppgandhi11
Helper V
Helper V

DAX formula help - Advanced level

Capture.PNG 

 

Hi, Above is my data set. I have color coded this by NPI column. I have put "advanced level" in the subject because I am very new to power bi, just started learning and have gotten this complex assignment.  The business logic for expected result is as follows.

 

If NPI A is selected, it has only 2 services: 1.1 and 1.2 so there are 2 rows in output for NPI A.

 

A has seen 3 patients with service 1.1 out of total 5 seen. So the "% Patients prescribed Service" is 3/5 * 100 in cell G2. With same logic it is 2/5 * 100 in cell G3 for service 1.2. -- This was simple. I am able to get this without problem.

 

A has prescribed service 1.1. Other providers who have prescribed service 1.1 are: B, D and E. 

B has seen total 3 patients, and prescribed 1 with service 1.1.

D has seen total 4 patients, and prescribed 2 with service 1.1.

E has seen total 4 patients, and prescribed 1 with service 1.1.

 

Hence, Total # of patients seen by other NPI with service 1.1 are: 1+2+1 = 4. Total # of patients seen by other NPI with all services are: 3+4+4 = 11.

 

So, the benchmark for NPI A (in relation to other NPI for same service) is: 4/11 * 100 in cell H2 for service 1.1

Similarly, benchmark for NPI A is: 6/10 * 100 in cell H3 for service 1.2 using NPI B, C and F since they are the only NPIs who have service 1.2

 

This is the part I am not able to get to. Can someone help me with this? I feel that a temp etc should be used that first of all captures the eligible NPI for finding benchmark since in above example, NPI  C and F were not used at all since they never prescribed service 1.1.  Any pointer is much appreciated. Thanks a lot!

 

Prashant-

1 ACCEPTED SOLUTION
Phil_Seamark
Employee
Employee

HI @ppgandhi11

 

I have built a PBIX for you which I have attached to this post, but here are the calculated measures that I think get pretty close.

 

% PATIENTS PRESCRIBED SERVICE = 
VAR NUMERATOR = COUNTROWS('Table1')
VAR DENOMINATOR = CALCULATE(COUNTROWS('Table1'), ALL('Table1'[SERVICE]))
RETURN DIVIDE(NUMERATOR,DENOMINATOR) * 100
BENCHMARK & = 
VAR NUMERATOR = 
    CALCULATE(
        COUNTROWS('Table1'),
        FILTER(
            ALL('Table1'),
            'Table1'[SERVICE] = MAX('Table1'[SERVICE]) &&
            'Table1'[NPI] <> MAX('Table1'[NPI])
            )
            )
VAR x = 
    SELECTCOLUMNS(
        FILTER(
            ALL('Table1'[SERVICE],'Table1'[NPI]),
            'Table1'[SERVICE] = MAX('Table1'[SERVICE]) &&
            'Table1'[NPI] <> MAX('Table1'[NPI])
            ),"ASDFASDF",[NPI])
            
VAR DENOMINATOR = COUNTROWS(GENERATE(x,FILTER(ALL(table1),[ASDFASDF] = 'Table1'[NPI])))        
        
RETURN  DIVIDE(NUMERATOR,DENOMINATOR) * 100

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

5 REPLIES 5
Phil_Seamark
Employee
Employee

HI @ppgandhi11

 

I have built a PBIX for you which I have attached to this post, but here are the calculated measures that I think get pretty close.

 

% PATIENTS PRESCRIBED SERVICE = 
VAR NUMERATOR = COUNTROWS('Table1')
VAR DENOMINATOR = CALCULATE(COUNTROWS('Table1'), ALL('Table1'[SERVICE]))
RETURN DIVIDE(NUMERATOR,DENOMINATOR) * 100
BENCHMARK & = 
VAR NUMERATOR = 
    CALCULATE(
        COUNTROWS('Table1'),
        FILTER(
            ALL('Table1'),
            'Table1'[SERVICE] = MAX('Table1'[SERVICE]) &&
            'Table1'[NPI] <> MAX('Table1'[NPI])
            )
            )
VAR x = 
    SELECTCOLUMNS(
        FILTER(
            ALL('Table1'[SERVICE],'Table1'[NPI]),
            'Table1'[SERVICE] = MAX('Table1'[SERVICE]) &&
            'Table1'[NPI] <> MAX('Table1'[NPI])
            ),"ASDFASDF",[NPI])
            
VAR DENOMINATOR = COUNTROWS(GENERATE(x,FILTER(ALL(table1),[ASDFASDF] = 'Table1'[NPI])))        
        
RETURN  DIVIDE(NUMERATOR,DENOMINATOR) * 100

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_Seamark

 

Hi Phil, So I created a whole new report with Import as opposed to Direct query. I could not find how to convert existing report from Direct Query to Import (searched through but did not have luck).

 

I then implemented your logic and it works exactly how I wanted. 100%. Thanks a lot.

 

Now, from learning perspective, I wanted to check with you how you used MAX function? That is a total mystery to me. It works, but I would like to understand it more.

 

For example: The numerator for numerator is:

 

VAR NUMERATOR =
    CALCULATE(
        COUNTROWS('Table1'),
        FILTER(
            ALL('Table1'),
            'Table1'[SERVICE] = MAX('Table1'[SERVICE]) &&
            'Table1'[NPI] <> MAX('Table1'[NPI])
            )
            )

 

How in above, it is considering all the services but not the irrelevant NPIs + the NPI in question itself? This is somehow taking all the eligible NPIs only in other words. I see you have used MAX to achieve this. can you please shed some light? When I read what max does, it simply says what one would expect: Returns Max value.  I would not have thought of using max function in remotests possibility to achieve this!  If you can throw some light, that would really help. The code looks much more cleaner now as well. Thanks again. 

 

Prashant-

Hi @ppgandhi11

 

I have a book that can help you with that at aka.ms/PracticalDax

 

But the quick answer is that the MAX() function is just shorthand for grabbing the value from the row (or column) header for filtering. 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

makes sense! Thanks. 🙂

Hi @Phil_Seamark

 

I created a test table with these values and tested it. It works. Thanks a lot.. I have a quick question. The universe I have is in directquery mode so it fails there saying: SELECTCOLUMN is not supported in directquery mode. do I need to change it to import mode? Sorry, I am very new to power bi so i do not know the difference.

 

even the sample data when imported as data connectivity mode = Import - it works. But when chosen as Direct query it fails with same error.

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.