DAX formula help - Easy in Excel, not so easy in Power BI

[PaulMac]

Ok, so, bare with me on this.

In excel I produced two pivot tables.

Table 1 counts how many complaints we received in the financial year

Table 2 counts how many complaints resolved at the 'point of contact'

I then do a nice simple formula (=A10/A5*1) if A10 is the count of complaints resolved at the 'point of contact' and A5 is the count of complaints we received in the financial year, times by 1 and I get the percentage of Complaints resolved at the 'point of contact'.

Tried having a go at creating a DAX measure in Power BI as I can't use a formula, but the DAX doesn't produce any figure at all (see attached image).

Here is my code:

PoC% = CALCULATE(COUNTROWS(All_Complaints),Date_Table[Date]<01/04/2018,All_Complaints[Resolved at Point of Contact]="Y",All_Complaints[Resolved at Point of Contact]="Yes")/CALCULATE(COUNTROWS(All_Complaints),All_Complaints[Status]="Closed",Date_Table[Date]<01/04/2018)*1

I am still very new to DAX and am struggling with it if I am honest.

Any assistance is greatly appreciated.

Kind regards

Paul

[Anonymous]

@PaulMac,

The filter arguements of the CALCULATE statement are evaluated in AND condition, so that's the reason why your measure always return blank. Please try this formula below instead:

PoC% =
CALCULATE (
    COUNTROWS ( All_Complaints ),
    Date_Table[Date] < 01 / 04 / 2018,
    OR (
        All_Complaints[Resolved at Point of Contact] = "Y",
        All_Complaints[Resolved at Point of Contact] = "Yes"
    )
)
    / CALCULATE (
        COUNTROWS ( All_Complaints ),
        All_Complaints[Status] = "Closed",
        Date_Table[Date] < 01 / 04 / 2018
    ) * 1

[PaulMac]

Hi @Anonymous 

I am afraid that your answer did not work. 😞

I copied and pasted the code you provided but I am still getting (Blank) for PoC% measure.

Do you have any other thoughts on what I might be able to try?

I look forward to your reply.

Kind regards

Paul

[PaulMac]

**BUMP**

Can no one find a solution to this seemingly simple issue?

[gooranga1]

Have you tried just creating 2 measures one counts all complaints and one that counts complaints resolved at 'point of contact'?

 Measure1 = CALCULATE (
        COUNTROWS ( All_Complaints ),
        All_Complaints[Status] = "Closed")

Measure2 = CALCULATE (
    COUNTROWS ( All_Complaints ),
    (
        All_Complaints[Resolved at Point of Contact] = "Y",
        All_Complaints[Resolved at Point of Contact] = "Yes"
    )
)

drop them into a table or matrix and see what they return?

[PaulMac]

Measure2 = CALCULATE (
    COUNTROWS ( All_Complaints ),
        All_Complaints[Resolved at Point of Contact] = "Y",
        All_Complaints[Resolved at Point of Contact] = "Yes"
    )

@gooranga1 

I did tweak your DAX. It looks like you had too many parentheis. But I am still not getting any value when using the above DAX code 

[gooranga1]

if you create another measue;

Measure2 = CALCULATE (
    COUNTROWS ( All_Complaints ))

And then put that into a table visual and screenshot the results

****

Maybe you need to create a new column in  All_Complaints table that creates one vakue of affirmative and use that instead;

newcol - if( All_Complaints[Resolved at Point of Contact] = "Y" ||
        All_Complaints[Resolved at Point of Contact] = "Yes","Yes","No")

[PaulMac]

@gooranga1 

That just give me this:

OK. Noww what do I do next?

Paul

[gooranga1]

I just was making sure there were no other filters.

Try adding a new column to the table that gives 1 value for the point of contact being true

newcol = if( All_Complaints[Resolved at Point of Contact] = "Y" ||
        All_Complaints[Resolved at Point of Contact] = "Yes","Yes","No")

then use 'newcol' as the filter for the rowcount.

[PaulMac]

@gooranga1  I did a replace values on the Resolved at Point of Contact column in power query now I just have "Y" or "N" .

I then used the previous code:

PoC% = 
CALCULATE (
    COUNTROWS ( All_Complaints ),
    Date_Table[Date] <= 01 / 04 / 2018,
    (
        All_Complaints[Resolved at Point of Contact] = "Y"
    )
)
    / CALCULATE (
        COUNTROWS ( All_Complaints ),
        All_Complaints[Status] = "Closed",
        Date_Table[Date] <= 01 / 04 / 2018
    ) * 1

Using the the "Y" as my one and only filter and still I get (Blank)

Any other ideas?

[gooranga1]

what happens when you put that measure in a matrix or table?

I would also split out the numerator and denominator of that calculation to see which part is failing or if any of them are returning anything.

[PaulMac]

@gooranga1 

The issue appears to be with using a second filter.

The following works correctly, but only has the 1 filter, I thought DAX was able tpo handle more than 1 filter.

Measure2 = CALCULATE (
    COUNTROWS ( All_Complaints ),
        All_Complaints[Resolved at Point of Contact] = "Yes")

Anyone have any thoughts?

Paul

[PaulMac]

Hi @gooranga1 

Thank you for your reply but your second measure does not work as it give the error stating

"Operator or expersion '()' is not supported in this context.

Any other ideas?

Regards

Paul

[PaulMac]

Hi @Anonymous 

I am afraid that your answer did not work.

I coppied and pasted the code you provided but I am still getting (Blank) for PoC% measure.

Do you have any other thoughts on what I might be able to try?

I look forward to your reply.

Kind regards

Paul