topic DAX- Addcolumn and Count with simple condition in DAX Commands and Tips

DAX- Addcolumn and Count with simple condition

ohnothimagain — Sun, 21 Jul 2024 04:48:10 GMT

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)

Re: DAX- Addcolumn and Count with simple condition

OwenAuger — Sun, 21 Jul 2024 08:37:05 GMT

Hi ohnothimagain

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 Result

The 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

Re: DAX- Addcolumn and Count with simple condition

ohnothimagain — Sun, 21 Jul 2024 14:26:48 GMT

＠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,

Re: DAX- Addcolumn and Count with simple condition

OwenAuger — Sun, 21 Jul 2024 14:49:48 GMT

ohnothimagain

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 Result

Does that work as intended?

Re: DAX- Addcolumn and Count with simple condition

ohnothimagain — Mon, 22 Jul 2024 16:14:30 GMT

Yes! it worked that I wanted it to be.thank you so much! It was really hard for me to solve by myself!