Forum Discussion
DAX- Addcolumn and Count with simple condition
Hello everyone,
I'm completly stuck in this problem,wish anyone can give me a solution...(maybe easy)
I have a table below,and want to count [PN]numbers only adapted distinct [PO] counts over 2.
then, created measure below,but doesn't work...(seems Row context is OK,but Grand todal row is incorrect)
How should I correct this measure?
■Table; TEST_
■Measure
test91:=var tbl=addcolumns(SUMMARIZE('TEST_','TEST_'[PN]),"@cnt",distinctcount('TEST_'[PO]))
var rst=countx(filter(tbl,[@cnt]>2),[@cnt])
return rst
■Result
Thank you for reading.
(my native language isn't English,sorry for inconvenient)
Thanks for testing this, and sorry for my typo! 😅
You're exactly right, the measure should be
test91 := VAR PN_PO = SUMMARIZE ( 'TEST_', 'TEST_'[PN], 'TEST_'[PO] ) VAR PN_PO_Count = GROUPBY ( PN_PO, 'TEST_'[PN], "@PO_Count", SUMX ( CURRENTGROUP (), 1 ) ) VAR PN_Filtered = FILTER ( PN_PO_COUNT, [@PO_Count] > 2 ) VAR Result = COUNTROWS ( PN_Filtered ) RETURN ResultDoes that work as intended?
4 Replies
I would recommend this:
test91 := VAR PN_PO = SUMMARIZE ( 'TEST_', 'TEST_'[PN], 'TEST_'[PO] ) VAR PN_PO_Count = GROUPBY ( PN_PO, "@PO_Count", SUMX ( CURRENTGROUP (), 1 ) ) VAR PN_Filtered = FILTER ( PN_PO_COUNT, [@PO_Count] > 2 ) VAR Result = COUNTROWS ( PN_Filtered ) RETURN ResultThe structure of the measure is similar to that shown in this article:
https://www.sqlbi.com/articles/apply-and-logic-to-multiple-selection-in-dax-slicer/
Does this work as intended?
Regards
＠OwenAuger
Thank you for responding quickly! (there's noone could in my coutry forums..)
I've copied your DAX and tried just now,Grand Total has changed but sadly something's still wrong..
I want to get value 3 but result is 1,could you give me more advice?
P.S.
I checked in foumulas one by one and figured out suspicious part :
Argument "TEST_PN" dropped off accidentally..?GROUPBY ( PN_PO, "@PO_Count", SUMX ( CURRENTGROUP (), 1 ) )
Regards,
Thanks for testing this, and sorry for my typo! 😅
You're exactly right, the measure should be
test91 := VAR PN_PO = SUMMARIZE ( 'TEST_', 'TEST_'[PN], 'TEST_'[PO] ) VAR PN_PO_Count = GROUPBY ( PN_PO, 'TEST_'[PN], "@PO_Count", SUMX ( CURRENTGROUP (), 1 ) ) VAR PN_Filtered = FILTER ( PN_PO_COUNT, [@PO_Count] > 2 ) VAR Result = COUNTROWS ( PN_Filtered ) RETURN ResultDoes that work as intended?
Yes! it worked that I wanted it to be.thank you so much! It was really hard for me to solve by myself!
