Calculate and count with three different column

[Saxon10]

I have a two tables are Data and Report.

Data

Data table contain the following three columns are Check, Supplier Status and Condition.

Report

Report table contain Supplier Status only.

Result

I am trying to get the count according to the supplier status based on the check (expect “NA”) and condition (=X) only from Data table to Report table.

Fillter:

I am trying to count Ok and Not Ok according to the Supplier status (expect “NA”) with condition =X

Data Table:

CHECK  SUPPLIER STATUS            CONDITION

OK          A1           X

OK          A1           X

OK          A1           X

OK          A1           X

OK          A1           X

NOT OK                A1           X

NOT OK                A1           X

NOT OK                A1           X

NOT OK                A1           X

NOT OK                A1           X

OK          A2           X

OK          A2           X

OK          A2           X

OK          A2           X

NOT OK                A2           X

NOT OK                A2           X

NOT OK                A2           X

NOT OK                A2           X

OK          A3           X

OK          A3           X

OK          A3           X

NOT OK                A3           X

NOT OK                A3           X

NOT OK                A3           X

OK          A4           X

OK          A4           X

NOT OK                A4           X

NOT OK                A4           X

OK          A5           X

NOT OK                A5           X

OK          MIXED  X

OK          MIXED  X

OK          MIXED  X

NOT OK                MIXED  X

OK          NA          NA

OK          NA          NA

OK          NA          NA

NOT OK                NA          NA

NOT OK                NA          NA

NOT OK                NA          NA

Report Table and Output:

SUPPLIER STATUS            NOT OK                OK

A1           5              5

A2           4              4

A3           3              3

A4           2              2

A5           1              1

MIXED  1              3

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.

[Saxon10]

thanks for your reply. Here is the PBI file for your reference https://www.dropbox.com/preview/CALCULATE%20and%20count%20based%20on%20the%20three%20different%20col...

[Ashish_Mathur]

That takes me to a log-in page.

[Saxon10]

Hi,

Thanks for your reply and Sorry for the trouble. I checked the file before share the link here but not sure what happen!

Herewith attached the excel file for your reference https://www.dropbox.com/scl/fi/7diipv1zhdcnjd5kz6nie/PBI_ASH.xlsx?dl=0&rlkey=wm8ntj4pqh7zgepyt8p8l10...

Can you please let me know if you able to access the file or not?

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Saxon10]

Hi,

Thanks for your reply. I already got the result in visual. I am looking for New calculated column (Dax). Can you please advise how can get the same result by using calculated column?

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Saxon10]

Thanks for your reply and help for new calculate column. Its working well. 

[Ashish_Mathur]

You are welcome.

[Saxon10]

I am applying the calculated column but giving different result OK  = CALCULATE(COUNT(DATA[SUPPLIER STATUS]),FILTER(ALL(DATA),DATA[SUPPLIER STATUS]=EARLIER(REPORT[SUPPLIER STATUS]) && CALCULATE(COUNT(DATA[CONDITION]),DATA[CHECK]="OK")))

Can you please advise on this. I can get the same result in visual messaure. 

I would like to get the same result in row level by using calculated column in report table.