Count with three different column in-between two tables in Power BI

Question

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.

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

Data

Desired Result:

SUPPLIER STATUS            NOT OK                OK

A1           5              5

A2           4              4

A3           3              3

A4           2              2

A5           1              1

MIXED  1              3

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

Answer 1

I would actually use a measure, not a calculated column. To get a measure filter results like you did in the visual, you need to use the combination of CALCULATE/FILTER functions.

https://learn.microsoft.com/en-us/dax/calculate-function-dax

https://learn.microsoft.com/en-us/dax/filter-function-dax

Count = CALCULATE(COUNTROWS(DATA), FILTER(DATA, DATA[CONDITION] = "X"))

Drop this measure in the values container of the matrix visual.

You can also have a separate measure for counting OK and NOR OK, like:

#Not OK = CALCULATE(COUNTROWS(DATA), FILTER(DATA, DATA[CONDITION] = "X" && DATA[SUPPLIER STATUS] = "NOT OK"))

#OK = CALCULATE(COUNTROWS(DATA), FILTER(DATA, DATA[CONDITION] = "X" && DATA[SUPPLIER STATUS] = "OK"))