Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
Share data in a format that can be pasted in an MS Excel file.
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...
That takes me to a log-in page.
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?
Hi,
You may download my PBI file from here.
Hope this helps.
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?
Hi,
You may download my PBI file from here.
Hope this helps.
Thanks for your reply and help for new calculate column. Its working well.
You are welcome.
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.
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
73 | |
60 | |
58 |