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
engyin2
Frequent Visitor

Need Help on DAX formula..

Hi 

I wrote the below measure in BI to have the result of the no. of reporting as shown in excel.  

Span = CALCULATE(COUNT(WD[Workday ID])) / CALCULATE(COUNT(WD[Is Manager]), FILTER(WD, WD[Is Manager] = "Yes"))

 

 But I am not sure how to 'tell' the BI to point back to the previous layer for the manager HC count. Can anyone help ? Thanks ! 

 

Capture.PNG

1 ACCEPTED SOLUTION

Hi @engyin2,

 

First your error, it is because that the Column Layer is a Text type column. It cannot be used to minus 1 directly. To resolve this error, you just need to change the column typle to whole number.

 

Then to achieve your requirement, you can try following measure:

 

Span =
CALCULATE (
    COUNT ( WD[Layer] ),
    FILTER ( ALL ( WD ), WD[Layer] = SELECTEDVALUE ( WD[Layer] ) + 1 )
)
    / CALCULATE (
        COUNT ( WD[Layer] ),
        FILTER ( WD, WD[Is Manager] = "Yes" ),
        ALLEXCEPT ( WD, WD[Layer] )
    )

111.PNG

 

Thanks,
Xi Jin.

View solution in original post

10 REPLIES 10
afzalphatan
Resolver I
Resolver I

Hi... 

First you need to change the Layer format to whole number format. 

 

Below is the dax formula and pic demonstrating the req result .....

 

Hope you enjoy it..

 

 

Span Final:=
Var Numerator = IF(HASONEVALUE(Table1[Layer]), 
CALCULATE(COUNTROWS(Table1), FILTER(ALL(Table1), Table1[Layer] = VALUES(Table1[Layer]) + 1))) Var Denominator = CALCULATE(COUNTROWS(Table1), Table1[Is Manager] = "Yes") Return DIVIDE(Numerator, Denominator)

 

 

Dax help.PNG

afzalphatan
Resolver I
Resolver I

Can you elaborate more on the base data...

 

where is coulumn header "Is manager" and "workday id"

Hi , 

 

WD id is the employee id. I am using it to count the HC by each department and job layer. 

And out of each department, I am identified if particular HC is a manger or not , hence column "is manager" will have "yes" or "no".

 

I would like to create the measure to see the no. of HC by department reporting to the manager in the previous layer but encountered problem to have the measure written. 

 

Appreciate your help and thanks.

Hi,

 

Share your source dataset and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ahish, 

 

Here is the example of the source and the expected result. Thanks.

Capture.PNG

 

Hi,

 

I cannot see the column headings - what is O9? N8?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Capture.PNG

Zubair_Muhammad
Community Champion
Community Champion

@engyin2

 

Try this MEASURE

 

Span =
CALCULATE ( COUNT ( WD[Workday ID] ) )
    / CALCULATE (
        COUNT ( WD[Is Manager] ),
        FILTER (
            ALL ( WD ),
            WD[Is Manager] = "Yes"
                && WD[Layer]
                    = SELECTEDVALUE ( WD[Layer] ) - 1
        )
    )

Regards
Zubair

Please try my custom visuals

Hi, 

 

Thanks for your help. But it dun really work. Any idea ? I have the below message prompted :

Capture.PNG

Hi @engyin2,

 

First your error, it is because that the Column Layer is a Text type column. It cannot be used to minus 1 directly. To resolve this error, you just need to change the column typle to whole number.

 

Then to achieve your requirement, you can try following measure:

 

Span =
CALCULATE (
    COUNT ( WD[Layer] ),
    FILTER ( ALL ( WD ), WD[Layer] = SELECTEDVALUE ( WD[Layer] ) + 1 )
)
    / CALCULATE (
        COUNT ( WD[Layer] ),
        FILTER ( WD, WD[Is Manager] = "Yes" ),
        ALLEXCEPT ( WD, WD[Layer] )
    )

111.PNG

 

Thanks,
Xi Jin.

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.