Forum Discussion
DAX Function for Count with filter
Hi everyone,
I am new to Power BI and I have no idea in complex DAX functions. I need to create a calculated column or measure (whichever is more appropriate) which counts unique combinations of companies + tags but with some applied rules.
| articleID | Company | Tag | Company + Tag | Count | Desired Count |
| 123a | ABC | Black | ABC - Black | 1 | 1 |
| 123a | ABC | Pink | ABC - Pink | 2 | - |
| 456b | DEF | Black | DEF - Black | 3 | 2 |
| 789c | XYZ | Pink | XYZ - Pink | 4 | 3 |
| 111d | AAA | White | AAA - White | 5 | 4 |
| 222e | BBB | Black | BBB - Black | 6 | 5 |
| 222e | BBB | Pink | BBB - Pink | 7 | - |
| 222e | BBB | White | BBB - White | 8 | 6 |
| Count Distinct - 8 | Desired Count - 6 |
In the sample table above, "PINK" should NOT be counted if and whenever it belongs to the same article as tag "BLACK". If they have the same article ID, BLACK should be the only one counted and Pink should be blank (articleIDs 123a and 222e). Pink can only be counted if it is a standalone tag (articleID 789c).
What is the DAX function for this scenario? Any help is appreciated. Thanks!
Hi powerbirookiee ,
Greg_Deckler's measure will only return 1 in subtoatl. Please try this code to create a measure.
Desired Count = VAR __ADD = ADDCOLUMNS ( 'Table', "Count", VAR _COUNTBYID = CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[articleID] ) ) RETURN SWITCH ( TRUE (), [Tag] = "Pink" && _COUNTBYID > 1, BLANK (), 1 ) ) RETURN SUMX ( __ADD, [Count] )Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
powerbirookiee So try this:
Desired Count = VAR __article = MAX('Table'[articleID]) VAR __tag = MAX('Table'[Tag]) VAR __tags = COUNTROWS(FILTER(ALL('Table'),[articleID] = __article)) VAR __Result = SWITCH(TRUE(), __tag = "Pink" && __tags > 1,BLANK(), 1 ) RETURN __ResultThen you will need this:
First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8Hi powerbirookiee ,
Greg_Deckler's measure will only return 1 in subtoatl. Please try this code to create a measure.
Desired Count = VAR __ADD = ADDCOLUMNS ( 'Table', "Count", VAR _COUNTBYID = CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[articleID] ) ) RETURN SWITCH ( TRUE (), [Tag] = "Pink" && _COUNTBYID > 1, BLANK (), 1 ) ) RETURN SUMX ( __ADD, [Count] )Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
