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

0
votes

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

enter image description here

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

enter image description here

1
if-statementcountpowerbidaxpowerbi-desktop
Hi. Can you please advise anyone regarding my query. – johon
And what solution have you tried so far that is not working? What is the purpose of the report table (and its relationship with Data table)? – W.B.
Thanks for your reply. I haven't tried anything so far. There is a relationship in-between two tables that's called the "Supplier Status". The "supplier status" are available in both tables. – johon
Please read here: stackoverflow.com/help/how-to-ask stackoverflow is not about asking for a solution, but about helping with a problem that you put in an effort to solve, but couldn't find a satisfying solution. – W.B.
Thanks for sharing the link. I search already and I can't found any similar solution that's the reason I posted here. " I haven't tried anything so far" that's means I am trying to crate formula my self". – johon

1 Answers

1
votes

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://docs.microsoft.com/en-us/dax/calculate-function-dax

https://docs.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"))