topic Re: Issue with count while converting reports from Cognos to power BI in DAX Commands and Tips

Issue with count while converting reports from Cognos to power BI

Anonymous — Tue, 28 Jul 2020 08:06:22 GMT

Hi All,

I have an issue while converting a Cognos Report to Power BI report.

Has Paid Amount in Term and AY: this is my Cognos statement
===============================
CASE
WHEN [Total Term and AY Paid Amt] is not null
AND [Total Term and AY Paid Amt] <> 0
THEN 'Y'
END

PowerBI: i am writing in Power BI
========
IF(NOT(ISBLANK(SELECTEDVALUE('Ross FNAD'[Award Paid Amt])))&& (SELECTEDVALUE('Ross FNAD'[Award Paid Amt])<>0),1)

Has Paid Amount in Multiple AYs(This is report col) This is Cognos
============================================
CASE
WHEN count([Has Paid Amount in Term and AY] for
[Student Performance BV].[Applicant Award Schedule].[Applicant Award Schedule PIDM],
[Student Performance BV].[Applicant Award Schedule].[Applicant Award Schedule Period]) >= 2
THEN 'Y'
END

Power BI: I am writing like this in Power Bi but i am getting fews rows as Y where as in Cognos its not Y for those. there is some thing wrong with this expression below i feel
========

if(CALCULATE(SUMX('Ross FNAD',[Paid Amt in Term]),'Ross FNAD'[PIDM],'Ross FNAD'[Term Code])>=2,"Y")

or
if(COUNTAX('Ross FNAD',[Paid Amt in Term])>=2 && CALCULATE(SUMX('Ross FNAD',[Paid Amt in Term])>=2),"Y")

Re: Issue with count while converting reports from Cognos to power BI

amitchandak — Tue, 28 Jul 2020 11:52:53 GMT

Anonymous , if you can have a new column. you can have like this

new column =if( not(isblank([Total Term and AY Paid Amt])) && [Total Term and AY Paid Amt] <> 0 , "Y")

Re: Issue with count while converting reports from Cognos to power BI

Anonymous — Tue, 28 Jul 2020 12:23:08 GMT

Hi Anonymous ,

WHEN [Total Term and AY Paid Amt] is not null
AND [Total Term and AY Paid Amt] <> 0
THEN 'Y'
END

If you are creating a Column

Column = IF('Ross FNAD'[Award Paid Amt] <> Blank() && 'Ross FNAD'[Award Paid Amt]<>0 , "Y")

If a measure

Measure = IF(MAX('Ross FNAD'[Award Paid Amt]) <> Blank() && MAX('Ross FNAD'[Award Paid Amt])<>0 , "Y")

For the second one you can try, thogh not very clear

Measure =
IF (
    COUNTX (
        'Ross FNAD',
        [Paid Amt in Term]
    ) >= 2,
    "Y"
)

Regards,

Harsh Nathani

Re: Issue with count while converting reports from Cognos to power BI

mschandra28 — Tue, 28 Jul 2020 12:25:36 GMT

Hi Amit,

I am sorry . You did not understand my issue .

I already have written a similar expression in Power BI as shown below.

IF(NOT(ISBLANK(SELECTEDVALUE('Ross FNAD'[Award Paid Amt])))&& (SELECTEDVALUE('Ross FNAD'[Award Paid Amt])<>0),1)

Award Paid Amt is a table column and the above DAX is for a new Measure which i created and named it as "Paid Amt in Term".

Why i wrote 1 because i am using this Paid Amount in Term and summing up or counting in next calculation.

Now i am creating a below calculation for getting Y. I am getting more Y's than expected.

if(CALCULATE(SUMX('Ross FNAD',[Paid Amt in Term]),'Ross FNAD'[PIDM],'Ross FNAD'[Term Code])>=2,"Y")

or
if(COUNTAX('Ross FNAD',[Paid Amt in Term])>=2 && CALCULATE(SUMX('Ross FNAD',[Paid Amt in Term])>=2),"Y")

FYI Total Term and AY Paid Amt is a Cognos Col and not there in Power BI

Re: Issue with count while converting reports from Cognos to power BI

mschandra28 — Tue, 28 Jul 2020 12:31:34 GMT

Hi Harsha,

I already used or wrote the same calculation as shown below. I am getting more "Y" 's than expected.

For some reason i am getting more Y's

if(COUNTAX('Ross FNAD',[Paid Amt in Term])>=2),"Y")

if(CALCULATE(SUMX('Ross FNAD',[Paid Amt in Term]),'Ross FNAD'[PIDM],'Ross FNAD'[Term Code])>=2,"Y")

Both the above DAX are giving me the same result set.